Need to display value based on two match criterion

**umayank** · 12-02-2011, 02:29 AM

HI,

I have an excel workbook containing two sheets SHEET1 and SHEET2. The data in Sheet1 looks as below

Item SO Source Column D
A 123 5654
B 65 777
C 55 888

And the data in Sheet2 is as below

SO Source Item Transaction ID
888 55 C 01232
5654 123 A 01332
777 65 B 03321

I want to compare Sheet1 and Sheet2 and compare the SO and Item in both the sheets and if it matches then display the corresponding value from Sheet2 Column Transaction ID in Column D of sheet1.

Please help me with this.

Regards

**zbor** · 12-02-2011, 03:05 AM

Here, try this:

=LOOKUP(9.999E+307,CHOOSE({1,2},0,INDEX(Sheet2!$D$1:$D$1000,MATCH(Sheet1!A2&Sheet1!B2, INDEX(Sheet2!$C$1:$C$1000&Sheet2!$B$1:$B$1000,,),0))))

**zbor** · 12-02-2011, 03:06 AM

This will return 0 if no match. if you want N/A use just:

=INDEX(Sheet2!$D$1:$D$1000,MATCH(Sheet1!A2&Sheet1!B2, INDEX(Sheet2!$C$1:$C$1000&Sheet2!$B$1:$B$1000,,),0))

Thread: Need to display value based on two match criterion

Thread Tools

Display

Need to display value based on two match criterion

Re: Need to display value based on two match criterion

Re: Need to display value based on two match criterion

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions