VLOOKUP+MATCH w/ Multiple Criteria

[mazlovski]

Is it somehow possible to use multpiple criteria for VLOOKUP+MATCH function combo? I'm trying to look up values by market. Because of the way the source file is set up I need to use two different criteria to bring in the correct value. I thought I could use AND(MATCH, MATCH) but that didn't work. Any suggestions?

	A	B	C	D
1		Total	Total	Total
2		US	Canada	Mexico
3	Sales Price

<tbody>
</tbody>

Thanks in advance.

 

[Aladin Akyurek]

=VLOOKUP("Sales Price",A:D,MATCH("Canada",INDEX(A:D,2,0),0),0)

Does this help? Note that the values US, Canda, etc. are assumed to be in row 2.

 

[mazlovski]

I don't think it will; I should have been more specific with the table. The source file looks more like this table below:

	A	B	C	D	E
1		Total	Product 1	Product 2	Total
2		US	Canada	Canada	Canada
3	Sales Price

<tbody>
</tbody>

It has values that build up Total US, Total Canada, etc. That's why I need two criteria to bring in the right value.

 

[Aladin Akyurek]

X2 = Product 2

Y2 = Canada

In Z2 control+shift+enter, not just enter:

=INDEX($B$3:$E$100,MATCH("Sales Price",$A$3:$A$100,0),MATCH(1,IF($B$1:$E$1=X2,IF($B$2:$E$2=Y2,1)),0))