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):-
[TABLE="width: 360"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Master Account#[/TD]
[TD]Buyer Account[/TD]
[TD]Seller Account[/TD]
[/TR]
[TR]
[TD]1ABCDG[/TD]
[TD]ABCD[/TD]
[TD]ABCD[/TD]
[/TR]
[TR]
[TD]1EFGHG[/TD]
[TD]EFGH[/TD]
[TD]EFGH[/TD]
[/TR]
[TR]
[TD]1IJKLG[/TD]
[TD]IJKL[/TD]
[TD]IJKL[/TD]
[/TR]
[TR]
[TD]1MNOPG[/TD]
[TD]MNOP-L[/TD]
[TD]MNOP-S[/TD]
[/TR]
</tbody>[/TABLE]


Formula Function to be applied (Sheet 2):-
[TABLE="width: 360"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD][TABLE="width: 360"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Buyer
Account[/TD]
[TD]Seller
Account[/TD]
[TD]Expected Result[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1ABCDG[/TD]
[TD]ABCD[/TD]
[/TR]
[TR]
[TD]1EFGHG[/TD]
[TD]0[/TD]
[TD]EFGH[/TD]
[/TR]
[TR]
[TD]1MNOPG[/TD]
[TD]0[/TD]
[TD]MNOP-L[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1MNOPG[/TD]
[TD]MNOP-S[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.