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]

 

[kweaver]

Would you be more specific about how the Raw Data translates to the output in Sheet2?

 

[DanteAmor]

Try this

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet2</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:136.87px;" /><col style="width:161.58px;" /><col style="width:131.17px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Buyer Account</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Seller Account</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Expected Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">0</td><td >1ABCDG</td><td >ABCD</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >1EFGHG</td><td style="text-align:right; ">0</td><td >EFGH</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >1MNOPG</td><td style="text-align:right; ">0</td><td >MNOP-L</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">0</td><td >1MNOPG</td><td >MNOP-S</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=VLOOKUP(IF(A2=0,B2,A2),Sheet1!$A$1:$C$5,IF(A2=0,3,2),0)</td></tr></table></td></tr></table>

 

[ericlzh]

Would you be more specific about how the Raw Data translates to the output in Sheet2?

Hi Kweaver,

Under Sheet 2, the Buyer & Seller account were mapped from other sheet tab i.e. Sheet 3 by using ='Sheet3'!A2.
As there is only either buyer or seller side, one column will be ''0'' and the other will appear the ''account code''

As of now, i need to lookup the account from Sheet 1 to Sheet 2 under the single column 'expected result'.

Hope this clarifies.

 

[kweaver]

Does Dante's formula do what you want?

 

[ericlzh]

Dante's formula works. Thx!

 

[ericlzh]

Thx Dante!

 

[DanteAmor]

Dante's formula works. Thx!

I'm glad to help you. Thanks for the feedback.

 

[ericlzh]

Hi Dante,

I am required to lookup additional value to get the same result, and I think Index & Match is a more appropriate formula to implement (I may need to also lookup more values in future to obtain same result).

Will you be able to show me how to work on the formula? I have provided the below data for our reference. Thanks!

(Raw Data)
Master Account Buyer Seller Product
1ABCDG ABCD-1 ABCD-1 Oil
1ABCDG ABCD-2 ABCD-2 Metals
1EFGHG EFGH-L EFGH-S Oil


(Trade Entry Worksheet)
Buyer Seller Traded product Expected Results
1ABCDG 0 Oil ABCD-1
0 1ABCDG Metals ABCD-2
0 1EFGHG Oil EFGH-S

 

[ericlzh]

I have re-post below for better clarity.
[TABLE="width: 583"]
<tbody fgid="48248">[TR]
[TD]
(Raw Data)
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Master Account
[/TD]
[TD]Buyer
[/TD]
[TD]Seller
[/TD]
[TD]Product
[/TD]
[/TR]
[TR]
[TD]1ABCDG
[/TD]
[TD]ABCD-1
[/TD]
[TD]ABCD-1
[/TD]
[TD]Oil
[/TD]
[/TR]
[TR]
[TD]1ABCDG
[/TD]
[TD]ABCD-2
[/TD]
[TD]ABCD-2
[/TD]
[TD]Metals
[/TD]
[/TR]
[TR]
[TD]1EFGHG
[/TD]
[TD]EFGH-L
[/TD]
[TD]EFGH-S
[/TD]
[TD]Oil
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](Trade Entry Worksheet)
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Buyer
[/TD]
[TD]Seller
[/TD]
[TD]Traded product
[/TD]
[TD]Expected Results
[/TD]
[/TR]
[TR]
[TD]1ABCDG
[/TD]
[TD]0
[/TD]
[TD]Oil
[/TD]
[TD]ABCD-1
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]1ABCDG
[/TD]
[TD]Metals
[/TD]
[TD]ABCD-2
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]1EFGHG
[/TD]
[TD]Oil
[/TD]
[TD]EFGH-S
[/TD]
[/TR]
</tbody>[/TABLE]