# How to compare/match 3 columns

This is a discussion on How to compare/match 3 columns within the Excel Questions forums, part of the Question Forums category; I've to compare two sheets with similar columns are : Sheet1: Column A (O/N), Column B ( P/N), Column C ...

1. ## How to compare/match 3 columns

I've to compare two sheets with similar columns are :
Sheet1: Column A (O/N), Column B ( P/N), Column C (QTY), Column D (Date)
Sheet2: Column A (QTY), Column B (O/N ), Column C (P/N)

All Columns are in text format except Column D which is in Date mm/dd/yy).

Here I have to compare these two sheets and return value of Coulmn D of sheet1 on Column E of sheet2 with following conditions:
-If cln A of Sht1 equal to cln B of sht2 then go to copare cln B of sht1 with cln C of sht2;
-if both match, then compare cln C of sht1 with cln A of sht2;
-if these three column are matched, return value of cln D of sht1 on cln E of sht 2

I've tried to use MATCH and INDEX formula, but fail at all. Should I use array formula to solve such conditions?

Btw, anyone can help to post formula here?

Tks a lot.

Regards,
CL

2. I think sumproduct formulas are way to go.

http://www.mrexcel.com/board2/viewto...899&highlight=

3. dtchan,

But i still don't know how to make it.
Could you post formula with example sheet here?

Regards,
CL

4. ## Re: How to compare/match 3 columns

Originally Posted by clwong
I've to compare two sheets with similar columns are :
Sheet1: Column A (O/N), Column B ( P/N), Column C (QTY), Column D (Date)
Sheet2: Column A (QTY), Column B (O/N ), Column C (P/N)

All Columns are in text format except Column D which is in Date mm/dd/yy).

Here I have to compare these two sheets and return value of Coulmn D of sheet1 on Column E of sheet2 with following conditions:
-If cln A of Sht1 equal to cln B of sht2 then go to copare cln B of sht1 with cln C of sht2;
-if both match, then compare cln C of sht1 with cln A of sht2;
-if these three column are matched, return value of cln D of sht1 on cln E of sht 2

I've tried to use MATCH and INDEX formula, but fail at all. Should I use array formula to solve such conditions?

Btw, anyone can help to post formula here?

Tks a lot.

Regards,
CL
Search for multikey lookup where an additional column is used to concatenate relevant columns.

Sorry, as I don't know what "Search for multikey lookup where an additional column is used to concatenate relevant columns." mean, could you give me more hits or a real example?

Yours,
CL

6. Originally Posted by clwong

Sorry, as I don't know what "Search for multikey lookup where an additional column is used to concatenate relevant columns." mean, could you give me more hits or a real example?

Yours,
CL
See my post in: http://www.mrexcel.com/board2/viewtopic.php?t=148849

Othwerwise, provide a 5-row sample from each sheet.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•