| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF formula within VLOOKUP (revisited)
Hi!
I saw that Roberto got a speedy answer to his problem, and mine is along the same lines, only with more tables to pick the information from (my tables are on separate sheets, but they don't have to be if absolutely necessary). I'm getting error messages that I am entering too many arguments in the formula, but I just can't figure out how to manipulate it any further. Here's the skinny: There are 2 tables per sheet on 5 worksheets, and 3 tables per sheet on 2 more worksheets. Total 16 tables over 7 sheets. Formula thus far: =IF(E37=2000,IF(F37=12,VLOOKUP(J21,'2000'!$A$14:$H $23,MATCH(K21,'2000'! $A$14:$H$14,0)),IF(F37=20,VLOOKUP(J21,'2000'!$A$26 :$H $35,MATCH(K21,'2000'!$A$26:$H$26,0)),IF(F37=0,VLOO KUP(J21,'2000'!$A $2:$H$11,MATCH(K21,'2000'!$A$2:$H $2,0))))),IF(E37=1000,IF(F37=12,VLOOKUP(J21,'1000' !$A$14:$H $23,MATCH(K21,'1000'!$A$14:$H$14,0)),IF(F37=20,VLO OKUP(J21,'1000'!$A $25:$H$34,MATCH(K21,'1000'!$A$25:$H$25,0)),IF(F37= 0,VLOOKUP(J21,'1000'! $A$2:$H$11,MATCH(K21,'1000'!$A$2:$H$2,0))))))) E37=2000 would be the altitude input from another cell. The possible settings for E37 are 0, 500, 1000, 2000, 4000, 6000 & 8000 F37=12 would be the flap setting. The possible settings for F37 are 0, 12 or 20 Weight variables in the tables are 16000-23500 lbs in 1000-lb increments (plus one more for the 23500 row) Temperature variables in the tables are -20 to +40 in 10-degree increments Now the formula goes into the worksheet named '2000' and does a vlookup in the A14:H23 range of the horizontal weight versus the vertical temperature after getting current-day weight (J21) and temperature (K21) from the user. That formula does the 1000 and 2000 foot altitude ranges, but I can't add any more. Need a solution for 500, 4000, 6000 and 8000 feet! Thanks very much!!! I'm in so far over my head! Craig |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF formula within VLOOKUP (revisited)
Looks like you're making your life much harder than you need to. The
easiest solution would be to copy all your tables to 1 table and then do a lookup against the 1 table. On Aug 15, 5:08 pm, TheMilkGuy wrote: Hi! I saw that Roberto got a speedy answer to his problem, and mine is along the same lines, only with more tables to pick the information from (my tables are on separate sheets, but they don't have to be if absolutely necessary). I'm getting error messages that I am entering too many arguments in the formula, but I just can't figure out how to manipulate it any further. Here's the skinny: There are 2 tables per sheet on 5 worksheets, and 3 tables per sheet on 2 more worksheets. Total 16 tables over 7 sheets. Formula thus far: =IF(E37=2000,IF(F37=12,VLOOKUP(J21,'2000'!$A$14:$H $23,MATCH(K21,'2000'! $A$14:$H$14,0)),IF(F37=20,VLOOKUP(J21,'2000'!$A$26 :$H $35,MATCH(K21,'2000'!$A$26:$H$26,0)),IF(F37=0,VLOO KUP(J21,'2000'!$A $2:$H$11,MATCH(K21,'2000'!$A$2:$H $2,0))))),IF(E37=1000,IF(F37=12,VLOOKUP(J21,'1000' !$A$14:$H $23,MATCH(K21,'1000'!$A$14:$H$14,0)),IF(F37=20,VLO OKUP(J21,'1000'!$A $25:$H$34,MATCH(K21,'1000'!$A$25:$H$25,0)),IF(F37= 0,VLOOKUP(J21,'1000'! $A$2:$H$11,MATCH(K21,'1000'!$A$2:$H$2,0))))))) E37=2000 would be the altitude input from another cell. The possible settings for E37 are 0, 500, 1000, 2000, 4000, 6000 & 8000 F37=12 would be the flap setting. The possible settings for F37 are 0, 12 or 20 Weight variables in the tables are 16000-23500 lbs in 1000-lb increments (plus one more for the 23500 row) Temperature variables in the tables are -20 to +40 in 10-degree increments Now the formula goes into the worksheet named '2000' and does a vlookup in the A14:H23 range of the horizontal weight versus the vertical temperature after getting current-day weight (J21) and temperature (K21) from the user. That formula does the 1000 and 2000 foot altitude ranges, but I can't add any more. Need a solution for 500, 4000, 6000 and 8000 feet! Thanks very much!!! I'm in so far over my head! Craig |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF formula within VLOOKUP (revisited)
I see what you're saying Tim, but each table has the same x&y data,
and the variables in the table themselves are different in every table... How can I fetch the PROPER cell when the weight up the y axis and the temperature on the x axis don't change? Or... perhaps I misunderstood you entirely. I'm befuddled right now, I must admit. Craig On Aug 15, 3:32 pm, Tim879 wrote: Looks like you're making your life much harder than you need to. The easiest solution would be to copy all your tables to 1 table and then do a lookup against the 1 table. On Aug 15, 5:08 pm, TheMilkGuy wrote: Hi! I saw that Roberto got a speedy answer to his problem, and mine is along the same lines, only with more tables to pick the information from (my tables are on separate sheets, but they don't have to be if absolutely necessary). I'm getting error messages that I am entering too many arguments in the formula, but I just can't figure out how to manipulate it any further. Here's the skinny: There are 2 tables per sheet on 5 worksheets, and 3 tables per sheet on 2 more worksheets. Total 16 tables over 7 sheets. Formula thus far: =IF(E37=2000,IF(F37=12,VLOOKUP(J21,'2000'!$A$14:$H $23,MATCH(K21,'2000'! $A$14:$H$14,0)),IF(F37=20,VLOOKUP(J21,'2000'!$A$26 :$H $35,MATCH(K21,'2000'!$A$26:$H$26,0)),IF(F37=0,VLOO KUP(J21,'2000'!$A $2:$H$11,MATCH(K21,'2000'!$A$2:$H $2,0))))),IF(E37=1000,IF(F37=12,VLOOKUP(J21,'1000' !$A$14:$H $23,MATCH(K21,'1000'!$A$14:$H$14,0)),IF(F37=20,VLO OKUP(J21,'1000'!$A $25:$H$34,MATCH(K21,'1000'!$A$25:$H$25,0)),IF(F37= 0,VLOOKUP(J21,'1000'! $A$2:$H$11,MATCH(K21,'1000'!$A$2:$H$2,0))))))) E37=2000 would be the altitude input from another cell. The possible settings for E37 are 0, 500, 1000, 2000, 4000, 6000 & 8000 F37=12 would be the flap setting. The possible settings for F37 are 0, 12 or 20 Weight variables in the tables are 16000-23500 lbs in 1000-lb increments (plus one more for the 23500 row) Temperature variables in the tables are -20 to +40 in 10-degree increments Now the formula goes into the worksheet named '2000' and does a vlookup in the A14:H23 range of the horizontal weight versus the vertical temperature after getting current-day weight (J21) and temperature (K21) from the user. That formula does the 1000 and 2000 foot altitude ranges, but I can't add any more. Need a solution for 500, 4000, 6000 and 8000 feet! Thanks very much!!! I'm in so far over my head! Craig- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF formula within VLOOKUP (revisited)
Without actually seeing your data, it's hard to make an intelligent
suggestion. If you want to keep the data laid out in the same way as you have it now, check out these 2 sites: http://www.decisionmodels.com/optspeede.htm - Three-dimensional lookup section (seems like the better choice) http://www.ozgrid.com/forum/showthread.php?t=46025 Both have tips on how to use lookup and choose to look up values in a selected table. Choose allows up to 29 choices so you should be ok with the # of tables you mentioned above. If you put all of the data in 1 spreadsheet, consider adding the table name to the X or Y data, (i.e. 5000-Weight1, 5000-Weight2....). This will allow you to have a unique index for your lookup. On Aug 15, 5:53 pm, TheMilkGuy wrote: I see what you're saying Tim, but each table has the same x&y data, and the variables in the table themselves are different in every table... How can I fetch the PROPER cell when the weight up the y axis and the temperature on the x axis don't change? Or... perhaps I misunderstood you entirely. I'm befuddled right now, I must admit. Craig On Aug 15, 3:32 pm, Tim879 wrote: Looks like you're making your life much harder than you need to. The easiest solution would be to copy all your tables to 1 table and then do a lookup against the 1 table. On Aug 15, 5:08 pm, TheMilkGuy wrote: Hi! I saw that Roberto got a speedy answer to his problem, and mine is along the same lines, only with more tables to pick the information from (my tables are on separate sheets, but they don't have to be if absolutely necessary). I'm getting error messages that I am entering too many arguments in the formula, but I just can't figure out how to manipulate it any further. Here's the skinny: There are 2 tables per sheet on 5 worksheets, and 3 tables per sheet on 2 more worksheets. Total 16 tables over 7 sheets. Formula thus far: =IF(E37=2000,IF(F37=12,VLOOKUP(J21,'2000'!$A$14:$H $23,MATCH(K21,'2000'! $A$14:$H$14,0)),IF(F37=20,VLOOKUP(J21,'2000'!$A$26 :$H $35,MATCH(K21,'2000'!$A$26:$H$26,0)),IF(F37=0,VLOO KUP(J21,'2000'!$A $2:$H$11,MATCH(K21,'2000'!$A$2:$H $2,0))))),IF(E37=1000,IF(F37=12,VLOOKUP(J21,'1000' !$A$14:$H $23,MATCH(K21,'1000'!$A$14:$H$14,0)),IF(F37=20,VLO OKUP(J21,'1000'!$A $25:$H$34,MATCH(K21,'1000'!$A$25:$H$25,0)),IF(F37= 0,VLOOKUP(J21,'1000'! $A$2:$H$11,MATCH(K21,'1000'!$A$2:$H$2,0))))))) E37=2000 would be the altitude input from another cell. The possible settings for E37 are 0, 500, 1000, 2000, 4000, 6000 & 8000 F37=12 would be the flap setting. The possible settings for F37 are 0, 12 or 20 Weight variables in the tables are 16000-23500 lbs in 1000-lb increments (plus one more for the 23500 row) Temperature variables in the tables are -20 to +40 in 10-degree increments Now the formula goes into the worksheet named '2000' and does a vlookup in the A14:H23 range of the horizontal weight versus the vertical temperature after getting current-day weight (J21) and temperature (K21) from the user. That formula does the 1000 and 2000 foot altitude ranges, but I can't add any more. Need a solution for 500, 4000, 6000 and 8000 feet! Thanks very much!!! I'm in so far over my head! Craig- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF formula within VLOOKUP (revisited)
< each table has the same x&y data, and the variables in the table themselves are different in every table.
In that case, I'd use MATCH() to find the two indexes and INDEX to retrieve the elements from the tables. You need the MATCH only once for each direction for all tables and INDEX is very fast. -- Kind regards, Niek Otten Microsoft MVP - Excel "TheMilkGuy" wrote in message ups.com... |I see what you're saying Tim, but each table has the same x&y data, | and the variables in the table themselves are different in every | table... How can I fetch the PROPER cell when the weight up the y | axis and the temperature on the x axis don't change? | | Or... perhaps I misunderstood you entirely. I'm befuddled right now, | I must admit. | | Craig | | On Aug 15, 3:32 pm, Tim879 wrote: | Looks like you're making your life much harder than you need to. The | easiest solution would be to copy all your tables to 1 table and then | do a lookup against the 1 table. | | On Aug 15, 5:08 pm, TheMilkGuy wrote: | | | | Hi! | | I saw that Roberto got a speedy answer to his problem, and mine is | along the same lines, only with more tables to pick the information | from (my tables are on separate sheets, but they don't have to be if | absolutely necessary). | | I'm getting error messages that I am entering too many arguments in | the formula, but I just can't figure out how to manipulate it any | further. | | Here's the skinny: | There are 2 tables per sheet on 5 worksheets, and 3 tables per sheet | on 2 more worksheets. Total 16 tables over 7 sheets. | | Formula thus far: | =IF(E37=2000,IF(F37=12,VLOOKUP(J21,'2000'!$A$14:$H $23,MATCH(K21,'2000'! | $A$14:$H$14,0)),IF(F37=20,VLOOKUP(J21,'2000'!$A$26 :$H | $35,MATCH(K21,'2000'!$A$26:$H$26,0)),IF(F37=0,VLOO KUP(J21,'2000'!$A | $2:$H$11,MATCH(K21,'2000'!$A$2:$H | $2,0))))),IF(E37=1000,IF(F37=12,VLOOKUP(J21,'1000' !$A$14:$H | $23,MATCH(K21,'1000'!$A$14:$H$14,0)),IF(F37=20,VLO OKUP(J21,'1000'!$A | $25:$H$34,MATCH(K21,'1000'!$A$25:$H$25,0)),IF(F37= 0,VLOOKUP(J21,'1000'! | $A$2:$H$11,MATCH(K21,'1000'!$A$2:$H$2,0))))))) | | E37=2000 would be the altitude input from another cell. The possible | settings for E37 are 0, 500, 1000, 2000, 4000, 6000 & 8000 | F37=12 would be the flap setting. The possible settings for F37 are | 0, 12 or 20 | | Weight variables in the tables are 16000-23500 lbs in 1000-lb | increments (plus one more for the 23500 row) | Temperature variables in the tables are -20 to +40 in 10-degree | increments | | Now the formula goes into the worksheet named '2000' and does a | vlookup in the A14:H23 range of the horizontal weight versus the | vertical temperature after getting current-day weight (J21) and | temperature (K21) from the user. | | That formula does the 1000 and 2000 foot altitude ranges, but I can't | add any more. | | Need a solution for 500, 4000, 6000 and 8000 feet! | | Thanks very much!!! I'm in so far over my head! | | Craig- Hide quoted text - | | - Show quoted text - | | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Greater than/Less than a certain Time (revisited) | Excel Worksheet Functions | |||
| Problems with autoformatting revisited | Excel Discussion (Misc queries) | |||
| Nested If statement revisited | Excel Worksheet Functions | |||
| using a macro question revisited | Excel Discussion (Misc queries) | |||
| Blank cell revisited | Excel Discussion (Misc queries) | |||
Linear Mode
