Return the nth array value based on two criterias

[Legacy 143009]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PRODUCT NAME[/TD]
[TD]TYPE[/TD]
[TD]BRAND[/TD]
[/TR]
[TR]
[TD]PRODUCT1[/TD]
[TD]A[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]PRODUCT2[/TD]
[TD]A[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]PRODUCT3[/TD]
[TD]B[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]PRODUCT4[/TD]
[TD]B[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]PRODUCT5[/TD]
[TD]B[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]


I have the table above. I want to match a product array wtih two conditions. And return the values in an order:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PRODUCT4[/TD]
[TD]PRODUCT5[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]PRODUCT1[/TD]
[TD]PRODUCT2[/TD]
[TD][/TD]
[TD]PRODUCT3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I've tried {=INDEX(Table1!A:A,MATCH(B1&A2;Table1!B:B&Table1!C:C,0))}
But it returns the first match value. When I copy to right it doesnt update product names.
For example nth value can be determined by column number.

Thanks

 

[Peter_SSs]

See if you can use this, copied across and down.

Excel Workbook
	A	B	C	D	E	F	G
1	PRODUCT NAME	TYPE	BRAND
2	PRODUCT1	A	Z
3	PRODUCT2	A	Z
4	PRODUCT3	B	Z
5	PRODUCT4	B	X
6	PRODUCT5	B	X
7
8
9		A	A	A	B	B	B
10	X					PRODUCT4	PRODUCT5
11	Z	PRODUCT1	PRODUCT2		PRODUCT3
Extract from array