| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
VLOOKUP?
I am a new user to Excel; am used to creating formulas in Lotus 123. I see
that there are more similarities than differences, however, I'm having trouble getting started. Scenario: I have four variables that require input into cells to return a number: Sales: B1 (there are three options: <= 25,000,000, = 25,000,001 but less than $75,000,000 and if = 75,000,000 "refer".) Price 1: B2 (this number is hard coded from another worksheet) Price 2: B3 (this number is hard coded from another worksheet) Amount: B4; (B4 is either 1,000,000, 2,000,000, 3,000,000, 4,000,000 or 5,000,000. I have also set up two tables that looks like this Sales <= $25,000,000 Table name: Factor 1 Limit Factor 1mm .10 2mm .15 3mm .20 4mm .25 5mm .30 Table name: Factor 2 Sales = 25,000,000 but less than 75,000,000 Limit Factor 1mm .10 2mm .15 3mm .25 4mm .30 5mm .50 If sales are less than or equal to 25,000,000 and 1,000,000 is shown in B4, I want to go to the first lookup table , select .10 and mulitply the number hard coded in B3 by that factor. I then want to add b2 and the result of this formula together. If sales are =25,000,001 but less than 75,000,000, I want to go to the second table and do the same calculation. I'm not sure where to start. I am used to @if statements and have tried: =if(b1,<=25,000,000,=if(b4=1,000,000,INDEX("FACTOR "*b3),=if(b4=$2,000,000,INDEX("factor"*b3) That's as far as I've gotten. The INDEX function appears to work the same way as lotus, but I can't get the thing to work properly. So: Sales $24,333,000 Price 1: $10,000 Price 2: $7,500 Limit: $2,000,000 would work out to .15 X $7,500 = $1,125. $1,125 would then be added to $10,000 for a total of $11,125. I'm getting caught up with the formulas. I'm so used to lotus, that I'm confusing myself when I try to get this to work. Could I ask someone to get me started? If I could get an idea of how the formulas are structured, I can build on that. I hope I haven't made this too confusing; any help would be very much appreciated! Thanks. Margie |
|
#2
|
|||
|
|||
|
|
|
#3
|
|||
|
|||
|
One way
Put in say, B5: =IF(B1<=25000000,VLOOKUP(B4/1000000&"mm",Factor1,2,0)*B3+B2,IF(AND(B1250000 00,B1<=75000000),VLOOKUP(B4/1000000&"mm",Factor2,2,0)*B3+B2,IF(B175000000," Refer",""))) where Factor1 and Factor2 are named ranges refering to: Factor1 (data below say, in D2:E6) 1mm .10 2mm .15 3mm .20 4mm .25 5mm .30 Factor2 (data below say, in G2:H6) 1mm .10 2mm .15 3mm .25 4mm .30 5mm .50 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Margie" wrote in message ... I am a new user to Excel; am used to creating formulas in Lotus 123. I see that there are more similarities than differences, however, I'm having trouble getting started. Scenario: I have four variables that require input into cells to return a number: Sales: B1 (there are three options: <= 25,000,000, = 25,000,001 but less than $75,000,000 and if = 75,000,000 "refer".) Price 1: B2 (this number is hard coded from another worksheet) Price 2: B3 (this number is hard coded from another worksheet) Amount: B4; (B4 is either 1,000,000, 2,000,000, 3,000,000, 4,000,000 or 5,000,000. I have also set up two tables that looks like this Sales <= $25,000,000 Table name: Factor 1 Limit Factor 1mm .10 2mm .15 3mm .20 4mm .25 5mm .30 Table name: Factor 2 Sales = 25,000,000 but less than 75,000,000 Limit Factor 1mm .10 2mm .15 3mm .25 4mm .30 5mm .50 If sales are less than or equal to 25,000,000 and 1,000,000 is shown in B4, I want to go to the first lookup table , select .10 and mulitply the number hard coded in B3 by that factor. I then want to add b2 and the result of this formula together. If sales are =25,000,001 but less than 75,000,000, I want to go to the second table and do the same calculation. I'm not sure where to start. I am used to @if statements and have tried: =if(b1,<=25,000,000,=if(b4=1,000,000,INDEX("FACTOR "*b3),=if(b4=$2,000,000,IN DEX("factor"*b3) That's as far as I've gotten. The INDEX function appears to work the same way as lotus, but I can't get the thing to work properly. So: Sales $24,333,000 Price 1: $10,000 Price 2: $7,500 Limit: $2,000,000 would work out to .15 X $7,500 = $1,125. $1,125 would then be added to $10,000 for a total of $11,125. I'm getting caught up with the formulas. I'm so used to lotus, that I'm confusing myself when I try to get this to work. Could I ask someone to get me started? If I could get an idea of how the formulas are structured, I can build on that. I hope I haven't made this too confusing; any help would be very much appreciated! Thanks. Margie |
|
#4
|
|||
|
|||
|
Thanks very much, Max. I'm playing around with the setup now and it seems to
be working just fine! Just needed a push in the right direction. Regards, Margie "Max" wrote: One way Put in say, B5: =IF(B1<=25000000,VLOOKUP(B4/1000000&"mm",Factor1,2,0)*B3+B2,IF(AND(B1250000 00,B1<=75000000),VLOOKUP(B4/1000000&"mm",Factor2,2,0)*B3+B2,IF(B175000000," Refer",""))) where Factor1 and Factor2 are named ranges refering to: Factor1 (data below say, in D2:E6) 1mm .10 2mm .15 3mm .20 4mm .25 5mm .30 Factor2 (data below say, in G2:H6) 1mm .10 2mm .15 3mm .25 4mm .30 5mm .50 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Margie" wrote in message ... I am a new user to Excel; am used to creating formulas in Lotus 123. I see that there are more similarities than differences, however, I'm having trouble getting started. Scenario: I have four variables that require input into cells to return a number: Sales: B1 (there are three options: <= 25,000,000, = 25,000,001 but less than $75,000,000 and if = 75,000,000 "refer".) Price 1: B2 (this number is hard coded from another worksheet) Price 2: B3 (this number is hard coded from another worksheet) Amount: B4; (B4 is either 1,000,000, 2,000,000, 3,000,000, 4,000,000 or 5,000,000. I have also set up two tables that looks like this Sales <= $25,000,000 Table name: Factor 1 Limit Factor 1mm .10 2mm .15 3mm .20 4mm .25 5mm .30 Table name: Factor 2 Sales = 25,000,000 but less than 75,000,000 Limit Factor 1mm .10 2mm .15 3mm .25 4mm .30 5mm .50 If sales are less than or equal to 25,000,000 and 1,000,000 is shown in B4, I want to go to the first lookup table , select .10 and mulitply the number hard coded in B3 by that factor. I then want to add b2 and the result of this formula together. If sales are =25,000,001 but less than 75,000,000, I want to go to the second table and do the same calculation. I'm not sure where to start. I am used to @if statements and have tried: =if(b1,<=25,000,000,=if(b4=1,000,000,INDEX("FACTOR "*b3),=if(b4=$2,000,000,IN DEX("factor"*b3) That's as far as I've gotten. The INDEX function appears to work the same way as lotus, but I can't get the thing to work properly. So: Sales $24,333,000 Price 1: $10,000 Price 2: $7,500 Limit: $2,000,000 would work out to .15 X $7,500 = $1,125. $1,125 would then be added to $10,000 for a total of $11,125. I'm getting caught up with the formulas. I'm so used to lotus, that I'm confusing myself when I try to get this to work. Could I ask someone to get me started? If I could get an idea of how the formulas are structured, I can build on that. I hope I haven't made this too confusing; any help would be very much appreciated! Thanks. Margie |
|
#6
|
|||
|
|||
|
You're welcome, Margie !
Glad to hear you got it working .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Margie" wrote in message ... Thanks very much, Max. I'm playing around with the setup now and it seems to be working just fine! Just needed a push in the right direction. Regards, Margie |
|
#7
|
|||
|
|||
|
glad to help
-- Don Guillett SalesAid Software "Margie" wrote in message ... Thanks Don. I'll give it a shot! Regards, Margie "Don Guillett" wrote: something like if(sales??,table1,table2) then vlookup(yourvalue,if(sales??,table1,table2),2,fal se) or you could combine tables and lookup vlookup(yourvalue,table,2+if(sales??,8,0),false) -- Don Guillett SalesAid Software "Margie" wrote in message ... I am a new user to Excel; am used to creating formulas in Lotus 123. I see that there are more similarities than differences, however, I'm having trouble getting started. Scenario: I have four variables that require input into cells to return a number: Sales: B1 (there are three options: <= 25,000,000, = 25,000,001 but less than $75,000,000 and if = 75,000,000 "refer".) Price 1: B2 (this number is hard coded from another worksheet) Price 2: B3 (this number is hard coded from another worksheet) Amount: B4; (B4 is either 1,000,000, 2,000,000, 3,000,000, 4,000,000 or 5,000,000. I have also set up two tables that looks like this Sales <= $25,000,000 Table name: Factor 1 Limit Factor 1mm .10 2mm .15 3mm .20 4mm .25 5mm .30 Table name: Factor 2 Sales = 25,000,000 but less than 75,000,000 Limit Factor 1mm .10 2mm .15 3mm .25 4mm .30 5mm .50 If sales are less than or equal to 25,000,000 and 1,000,000 is shown in B4, I want to go to the first lookup table , select .10 and mulitply the number hard coded in B3 by that factor. I then want to add b2 and the result of this formula together. If sales are =25,000,001 but less than 75,000,000, I want to go to the second table and do the same calculation. I'm not sure where to start. I am used to @if statements and have tried: =if(b1,<=25,000,000,=if(b4=1,000,000,INDEX("FACTOR "*b3),=if(b4=$2,000,000,IN DEX("factor"*b3) That's as far as I've gotten. The INDEX function appears to work the same way as lotus, but I can't get the thing to work properly. So: Sales $24,333,000 Price 1: $10,000 Price 2: $7,500 Limit: $2,000,000 would work out to .15 X $7,500 = $1,125. $1,125 would then be added to $10,000 for a total of $11,125. I'm getting caught up with the formulas. I'm so used to lotus, that I'm confusing myself when I try to get this to work. Could I ask someone to get me started? If I could get an idea of how the formulas are structured, I can build on that. I hope I haven't made this too confusing; any help would be very much appreciated! Thanks. Margie |
|
#8
|
|||
|
|||
|
Hi Don:
I've made a mess; my ignorance of excel is shining through bright and clear. I wonder if you could help, once again. I tried your solution to my query and can't seem to get it to work. I stumbled across a "lookup wizard" that actually created an Index formula based on a "table?" as follows: Limit Factor 1mm .10 2mm .20 3mm .30 4mm .40 5mm .50 The formula is INDEX($d$1:$E$6,MATCH(sheet2!e10,$D$1:$D$6,),MATCH (D8,$D$1:$E$1,)). This works fine; if I put 5mm in D10, it pulls the rate .50. Where I'm running into problems is with the "if" statements. The table above is for sales <=$25mm. Do I have to set up two additional "tables" for sales =$25mm and =$75mm? If so, how do I get the formula to go to the right table? Do I use an IF statement? Bascially, I want to say if sales are less than $25mm, go to table 1, they are greater than $25mm, go to table 2 and then, if they are greater than $75mm, "refer". Do I have to name the range where the tables are located (it's the Lotus in me talking) and have the if statement point in the right direction? I'm getting my applications mixed up. If, then,else.....I'm sure the logic's the same, it's just the "language". Any help you can give me would be greatly appreciated! Thanks Don. Marg "Don Guillett" wrote: glad to help -- Don Guillett SalesAid Software "Margie" wrote in message ... Thanks Don. I'll give it a shot! Regards, Margie "Don Guillett" wrote: something like if(sales??,table1,table2) then vlookup(yourvalue,if(sales??,table1,table2),2,fal se) or you could combine tables and lookup vlookup(yourvalue,table,2+if(sales??,8,0),false) -- Don Guillett SalesAid Software "Margie" wrote in message ... I am a new user to Excel; am used to creating formulas in Lotus 123. I see that there are more similarities than differences, however, I'm having trouble getting started. Scenario: I have four variables that require input into cells to return a number: Sales: B1 (there are three options: <= 25,000,000, = 25,000,001 but less than $75,000,000 and if = 75,000,000 "refer".) Price 1: B2 (this number is hard coded from another worksheet) Price 2: B3 (this number is hard coded from another worksheet) Amount: B4; (B4 is either 1,000,000, 2,000,000, 3,000,000, 4,000,000 or 5,000,000. I have also set up two tables that looks like this Sales <= $25,000,000 Table name: Factor 1 Limit Factor 1mm .10 2mm .15 3mm .20 4mm .25 5mm .30 Table name: Factor 2 Sales = 25,000,000 but less than 75,000,000 Limit Factor 1mm .10 2mm .15 3mm .25 4mm .30 5mm .50 If sales are less than or equal to 25,000,000 and 1,000,000 is shown in B4, I want to go to the first lookup table , select .10 and mulitply the number hard coded in B3 by that factor. I then want to add b2 and the result of this formula together. If sales are =25,000,001 but less than 75,000,000, I want to go to the second table and do the same calculation. I'm not sure where to start. I am used to @if statements and have tried: =if(b1,<=25,000,000,=if(b4=1,000,000,INDEX("FACTOR "*b3),=if(b4=$2,000,000,IN DEX("factor"*b3) That's as far as I've gotten. The INDEX function appears to work the same way as lotus, but I can't get the thing to work properly. So: Sales $24,333,000 Price 1: $10,000 Price 2: $7,500 Limit: $2,000,000 would work out to .15 X $7,500 = $1,125. $1,125 would then be added to $10,000 for a total of $11,125. I'm getting caught up with the formulas. I'm so used to lotus, that I'm confusing myself when I try to get this to work. Could I ask someone to get me started? If I could get an idea of how the formulas are structured, I can build on that. I hope I haven't made this too confusing; any help would be very much appreciated! Thanks. Margie |
|
#9
|
|||
|
|||
|
Why not just ONE table?? It's not clear why you need more. Perhaps a very
clear explanation would help. I would offer to let you send me a workbook but I will be out of the office all of tomorrow. But, if all else fails send a SMALL wb anyway. -- Don Guillett SalesAid Software "Margie" wrote in message ... Hi Don: I've made a mess; my ignorance of excel is shining through bright and clear. I wonder if you could help, once again. I tried your solution to my query and can't seem to get it to work. I stumbled across a "lookup wizard" that actually created an Index formula based on a "table?" as follows: Limit Factor 1mm .10 2mm .20 3mm .30 4mm .40 5mm .50 The formula is INDEX($d$1:$E$6,MATCH(sheet2!e10,$D$1:$D$6,),MATCH (D8,$D$1:$E$1,)). This works fine; if I put 5mm in D10, it pulls the rate .50. Where I'm running into problems is with the "if" statements. The table above is for sales <=$25mm. Do I have to set up two additional "tables" for sales =$25mm and =$75mm? If so, how do I get the formula to go to the right table? Do I use an IF statement? Bascially, I want to say if sales are less than $25mm, go to table 1, they are greater than $25mm, go to table 2 and then, if they are greater than $75mm, "refer". Do I have to name the range where the tables are located (it's the Lotus in me talking) and have the if statement point in the right direction? I'm getting my applications mixed up. If, then,else.....I'm sure the logic's the same, it's just the "language". Any help you can give me would be greatly appreciated! Thanks Don. Marg "Don Guillett" wrote: glad to help -- Don Guillett SalesAid Software "Margie" wrote in message ... Thanks Don. I'll give it a shot! Regards, Margie "Don Guillett" wrote: something like if(sales??,table1,table2) then vlookup(yourvalue,if(sales??,table1,table2),2,fal se) or you could combine tables and lookup vlookup(yourvalue,table,2+if(sales??,8,0),false) -- Don Guillett SalesAid Software "Margie" wrote in message ... I am a new user to Excel; am used to creating formulas in Lotus 123. I see that there are more similarities than differences, however, I'm having trouble getting started. Scenario: I have four variables that require input into cells to return a number: Sales: B1 (there are three options: <= 25,000,000, = 25,000,001 but less than $75,000,000 and if = 75,000,000 "refer".) Price 1: B2 (this number is hard coded from another worksheet) Price 2: B3 (this number is hard coded from another worksheet) Amount: B4; (B4 is either 1,000,000, 2,000,000, 3,000,000, 4,000,000 or 5,000,000. I have also set up two tables that looks like this Sales <= $25,000,000 Table name: Factor 1 Limit Factor 1mm .10 2mm .15 3mm .20 4mm .25 5mm .30 Table name: Factor 2 Sales = 25,000,000 but less than 75,000,000 Limit Factor 1mm .10 2mm .15 3mm .25 4mm .30 5mm .50 If sales are less than or equal to 25,000,000 and 1,000,000 is shown in B4, I want to go to the first lookup table , select .10 and mulitply the number hard coded in B3 by that factor. I then want to add b2 and the result of this formula together. If sales are =25,000,001 but less than 75,000,000, I want to go to the second table and do the same calculation. I'm not sure where to start. I am used to @if statements and have tried: =if(b1,<=25,000,000,=if(b4=1,000,000,INDEX("FACTOR "*b3),=if(b4=$2,000,000,IN DEX("factor"*b3) That's as far as I've gotten. The INDEX function appears to work the same way as lotus, but I can't get the thing to work properly. So: Sales $24,333,000 Price 1: $10,000 Price 2: $7,500 Limit: $2,000,000 would work out to .15 X $7,500 = $1,125. $1,125 would then be added to $10,000 for a total of $11,125. I'm getting caught up with the formulas. I'm so used to lotus, that I'm confusing myself when I try to get this to work. Could I ask someone to get me started? If I could get an idea of how the formulas are structured, I can build on that. I hope I haven't made this too confusing; any help would be very much appreciated! Thanks. Margie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| make a vlookup using a variable path | Excel Worksheet Functions | |||
| vlookup data hidden within worksheet | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
| carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
| VLOOKUP not working | Excel Worksheet Functions | |||
Linear Mode
