IF formula within VLOOKUP (revisited)

[TheMilkGuy]

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,VLOOKUP(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,VLOOKUP(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

 

[Tim879]

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.

 

[TheMilkGuy]

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

 

[Tim879]

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.

 

[Niek Otten]

< 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

|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
|
| > 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.
| >
| >
| >
| >
| > > 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,VLOOKUP(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,VLOOKUP(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 -
|
|