lookups with multiple lookup values
 

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?

lookups with multiple lookup values
 

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pepsinmentos" wrote:
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?

lookups with multiple lookup values
 

Hi pepsinmentos,

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

HTH
Martin



"pepsinmentos" wrote in message
...
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?

lookups with multiple lookup values
 

Thank you very much :)

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pepsinmentos" wrote:
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?

lookups with multiple lookup values
 

welcome
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pepsinmentos" wrote in message
...
Thank you very much :)