Index match with multiple criteria - please help!

[nicray]

Hi

Please can anyone help me with the formula for INDEX MATCH with two lots of criteria.

Data:

Site	Code	Supplier
A	A0001	Company X
B	A0001	Company Y
C	A0001	Company C
D	A0001	Company Y
B	A0002	Company X
B	A0003	Company A
D	A0002	Company B

<tbody>
</tbody>

Criteria:

Site	Code	Supplier/RESULT
A	A0001	Company X
B	A0001	Company Y
C	A0001	Company C
D	A0001	Company Y
B	A0002	0
B	A0003	0
D	A0002	#REF!

<tbody>
</tbody>

I've used the formula below but there seems to be a problem.
=INDEX($C$2:$C$13,MATCH(A17,$A$2:$A$13,0)*MATCH(B17,$B$2:$B$13,0))

Can anyone tell me where I am going wrong or if there is a better formula to use. My actual data is 000s of rows.

 

[Scott T]

Is this what you want?

Excel 2010
	A	B	C
1	Site	Code	Supplier
2	A	A0001	Company X
3	B	A0001	Company Y
4	C	A0001	Company C
5	D	A0001	Company Y
6	B	A0002	Company X
7	B	A0003	Company A
8	D	A0002	Company B
9
10
11
12
13
14
15
16	Site	Code	Supplier/RESULT
17	A	A0001	Company X
18	B	A0001	Company Y
19	C	A0001	Company C
20	D	A0001	Company Y
21	B	A0002	Company X
22	B	A0003	Company A
23	D	A0002	Company B
Sheet4

Cell Formulas
Range	Formula
C17	{=INDEX($C$2:$C$8,MATCH(A17&B17,$A$2:$A$8&$B$2:$B$8,0))}
C18	{=INDEX($C$2:$C$8,MATCH(A18&B18,$A$2:$A$8&$B$2:$B$8,0))}
C19	{=INDEX($C$2:$C$8,MATCH(A19&B19,$A$2:$A$8&$B$2:$B$8,0))}
C20	{=INDEX($C$2:$C$8,MATCH(A20&B20,$A$2:$A$8&$B$2:$B$8,0))}
C21	{=INDEX($C$2:$C$8,MATCH(A21&B21,$A$2:$A$8&$B$2:$B$8,0))}
C22	{=INDEX($C$2:$C$8,MATCH(A22&B22,$A$2:$A$8&$B$2:$B$8,0))}
C23	{=INDEX($C$2:$C$8,MATCH(A23&B23,$A$2:$A$8&$B$2:$B$8,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Legacy 143009]

I dont get what you expect exact but may be:

=INDEX($C$2:$C$13,MATCH(A17&B17,$A$2:$A$13&$B$2:$B$13,0))

I think it's an array formula so Ctrl+Shift+Enter