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:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Site
[/TD]
[TD]Code
[/TD]
[TD]Supplier
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]A0001
[/TD]
[TD]Company X
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]A0001
[/TD]
[TD]Company Y
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]A0001
[/TD]
[TD]Company C
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]A0001
[/TD]
[TD]Company Y
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]A0002
[/TD]
[TD]Company X
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]A0003
[/TD]
[TD]Company A
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]A0002
[/TD]
[TD]Company B
[/TD]
[/TR]
</tbody>[/TABLE]

Criteria:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Site
[/TD]
[TD]Code
[/TD]
[TD]Supplier/RESULT
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]A0001
[/TD]
[TD]Company X
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]A0001
[/TD]
[TD]Company Y
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]A0001
[/TD]
[TD]Company C
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]A0001
[/TD]
[TD]Company Y
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]A0002
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]A0003
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]A0002
[/TD]
[TD]#REF!
[/TD]
[/TR]
</tbody>[/TABLE]

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