Multi-column lookup

[John Broderick]

Lets say I have a table with 3 columns of independent variables and one
column of dependent data.
An example might be:
Shape Color Size Price
-------------------------------
Square Red Small 1.50
Circle Green Med 2.00
Triangle Green Small 4.50


How do I lookup the price of (say) a Medium Green Circle?

 

[Peo Sjoblom]

=INDEX(Price_Range,MATCH(1,(Size_Range="Med")*(Colour_Range="Green")*(Shape_Range="Circle"),0))


entered with ctrl + shift & enter

change the hard coded values like "Green" etc to cell references where you
would put them instead, that way you don't need top edit the formula if you
change criteria

 

[Peo Sjoblom]

Another way

=SUMPRODUCT(--(Size_Range="Medium"),--(Colour_Range="Green"),--(Shape_Range="Circle"),Prize_Range)

This is more efficient than the first formula I gave you but it can only
return a number but since that is what you want it should be good to go

 

[John Broderick]

Thanks, both work.
Very clever. I should look into these array formulas a bit deeper.

Regards
John