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>

 

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

(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

<tbody fgid="48248">
</tbody>