lookups with multiple lookup values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If I have a list of products that each come in a different colour and have a
different price, how can I look up the price using both values 'Product' and
'Colour'?

ex.

A B C
1 Car Red 1000
2 Car Blue 2000
3 Bike Orange 500
4 Bike Red 700
5 Bike Blue 1000

Now, if I have a cell with Car and a cell with Red.... How do I get the
corrosponding price?
 
One way ..

Assume source data in cols A to C, from row2 down

Product and color inputs assumed made in E2:F2 down
eg in E2:F2 is: Car, Blue

Put in G2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(COUNTA(E2:F2)<2,"",INDEX($C$2:$C$100,MATCH(1,($A$2:$A$100=E2)*($B$2:$B$100=F2),0)))
G2 will return the required result from the source col C for the inputs in
E2:F2, viz: 2000 in the example instance. Copy G2 down as far as required.
 
Thank you very much :)

Max said:
One way ..

Assume source data in cols A to C, from row2 down

Product and color inputs assumed made in E2:F2 down
eg in E2:F2 is: Car, Blue

Put in G2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(COUNTA(E2:F2)<2,"",INDEX($C$2:$C$100,MATCH(1,($A$2:$A$100=E2)*($B$2:$B$100=F2),0)))
G2 will return the required result from the source col C for the inputs in
E2:F2, viz: 2000 in the example instance. Copy G2 down as far as required.
Click to expand...
 
Back
Top