nested lookups
Posted by berney hall on November 19, 2001 2:17 AM
is it possible to base the value of a cell on lookups from 3 others? for example, the spreadsheet needs to look at one table depending on if the material is marble, granite or limestone, then another one for the name of the stone, then another one for the thickness to calculate a price per square metre? would this use a lookup or find command?
thanks if u can give me any help with this
Posted by JAF on November 19, 2001 3:56 AM
As far as I know, you can't specify multiple criteria in a LOOKUP formula.
What you can do is to create a lookup table with just 2 columns containing all your variables as follows:
Marble-A 25.23
Marble-B 35.17
Marble-C 45.27
Granite-A 12.23
Granite-B 23.00
etc.
Then, assuming your Type and name are in Columns A and B, you create the following lookup formula:
=VLOOKUP(A2&"-"&B2,$E$1:$F$20,2)
where $E$2:$F$20 is the loation of your lokup table.
NB:, you don't have to seperate A and B with the hyphen, it just makes it easier to see what's what. Granite-A is a lot easier to read than GraniteA !!
