Multi-column lookup

  • Thread starter Thread starter John Broderick
  • Start date Start date
J

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?
 
=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
 
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
 
Thanks, both work.
Very clever. I should look into these array formulas a bit deeper.

Regards
John
 
Back
Top