lookups with multiple lookup values

[pepsinmentos]

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?

 

[Max]

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.

 

[MartinW]

Hi pepsinmentos,

You may also want to take a look here,
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Martin

 

[pepsinmentos]

Thank you very much

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.

 

[Max]

welcome