Index match function

[ericlzh]

Dear All,

I could like to use INDEX MATCH function to obtain a specific data that i need from Raw Data. How can i implement the formula?
Please refer to example below.
Thank you all.

Raw Data (Sheet 1):-

Master Account#	Buyer Account	Seller Account
1ABCDG	ABCD	ABCD
1EFGHG	EFGH	EFGH
1IJKLG	IJKL	IJKL
1MNOPG	MNOP-L	MNOP-S

<colgroup><col span="3"></colgroup><tbody>
</tbody>

Formula Function to be applied (Sheet 2):-

Buyer Account Seller Account Expected Result 0 1ABCDG ABCD 1EFGHG 0 EFGH 1MNOPG 0 MNOP-L 0 1MNOPG MNOP-S <colgroup><col span="3"></colgroup><tbody> </tbody>

<colgroup><col span="3"></colgroup><tbody>
</tbody>

 

[Aryatect]

Hi, Hopefully this would work:

Book1
	A	B	C	D
1	Buyer	Seller	Traded product	Expected Results
2	1ABCDG	0	Oil	ABCD-1
3	0	1ABCDG	Metals	ABCD-2
4	0	1EFGHG	Oil	EFGH-S
Sheet2

Cell Formulas
Range	Formula
D2	{=INDEX(Sheet1!$C$3:$C$5,MATCH(IF(A2=0,B2,A2)&C2,Sheet1!$A$3:$A$5&Sheet1!$D$3:$D$5,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DanteAmor]

Try regular formula

Book1
	A	B	C	D
1	Buyer	Seller	Traded product	Expected Results
2	1ABCDG	0	Oil	ABCD-1
3	0	1ABCDG	Metals	ABCD-2
4	0	1EFGHG	Oil	EFGH-S
Sheet2

Cell Formulas
Range	Formula
D2	=INDEX(Sheet1!$B$2:$C$4,SUMPRODUCT((Sheet1!$A$2:$A$4=IF(A2<>0,A2,B2))*(Sheet1!$D$2:$D$4=C2)*(ROW(Sheet1!$D$2:$D$4)))-1,IF(A2<>0,1,2))

 

[ericlzh]

Hi Dante,

The formula works. Thx!

 

[ericlzh]

Hi Aryatect,

Your formula works fine but it can only applied to the Seller Leg and not the Buyer Leg.

Hence, I am applying using Dante's formula. Thx!

 

[DanteAmor]

Hi Dante,

The formula works. Thx!

Im glad to help you, thanks for the feedback.