compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

**LearnerEXL** · 09-24-2009, 03:27 AM

Dear Friends,

I have one workbook (Excel 2007) with 2 sheets. Sheet-1 contains 4 columns like the following:

Excel Sheet 1
COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4
1005 10 Sam Royal Blue
1005 20 Peter Navy Blue
1006 50 Mary Rose
1100 40 Sonam Green
1156 80 Rashmi Pink

And the Sheet -2 also contains 4 columns. The result should be like the following:

RESULT
Excel Sheet 2
COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4
997 90 Rupa
1005 10 Sam Royal Blue
1100 40 Sonam Green
1100 40 Sonam Green
1156 80 Rashmi Pink
886 30 Vanaja
775 50 Vivek
1005 20 Peter Navy Blue
1006 50 Mary Rose
1006 60 Varun
1100 40 Sonam Green
1005 40 Priya
1156 80 Rashmi Pink

What I need now is, if the Sheet-2 (first 3 columns) data match with the Sheet-1 (first 3 columns) data, then the fourth column of Sheet-2 to be automatically filled with Sheet-1 fourth column data.

Please do help.

The following works fine with single column checking. I need it with 3 columns checking.

=VLOOKUP(A14,Sheet2!$A$2:$B$5,2,FALSE)

Thanks in advance.

**DonkeyOte** · 09-24-2009, 03:38 AM

Post a sample workbook - where spaces are involved in strings it's impossible for us to determine whether a word appears in one column or another.... as it stands I'm not sure you list any values in Column 4 on sheet 1... and it's important we know if said values are numeric or text so to reiterate post a sample file.

**DonkeyOte** · 09-24-2009, 03:49 AM

Perhaps in the meantime you mean along the lines of:

Code:

Sheet2!D2:
=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",LOOKUP(2,1/((Sheet1!$A$2:$A$6&Sheet1!$B$2:$B$6&Sheet1!$C$2:$C$6=$A2&$B2&$C2)),Sheet1!$D$2:$D$6)))
copied down
change ranges to suit

You will invariably find it easier to concatenate the values of interest into one column and conduct a simple INDEX/MATCH using the concatenation column, eg:

Code:

Sheet1!E2:
=$A2&":"&$B2&":"&$C2
copied down to E6

Sheet2!D2:
=IF(ISNA(MATCH($A2&":"&$B2&":"&$C2,Sheet1!$E$2:$E$6,0)),"",INDEX(Sheet1!$D$2:$D$6,MATCH($A2&":"&$B2&":"&$C2,Sheet1!$E$2:$E$6,0)))
copied down

This is perhaps a little more straightforward to follow than the earlier LOOKUP method and will be more efficient should you have large volumes of data in your real file.

**LearnerEXL** · 09-24-2009, 04:32 AM

Originally Posted by DonkeyOte

Post a sample workbook - where spaces are involved in strings it's impossible for us to determine whether a word appears in one column or another.... as it stands I'm not sure you list any values in Column 4 on sheet 1... and it's important we know if said values are numeric or text so to reiterate post a sample file.

Please find attached the workbook.

Thanks.

**DonkeyOte** · 09-24-2009, 04:34 AM

Thanks - you should find post # 3 suggestions can be applied to your file.

**LearnerEXL** · 09-24-2009, 04:46 AM

Originally Posted by DonkeyOte

Thanks - you should find post # 3 suggestions can be applied to your file.

Thanks. Could you please write code in my sample sheet so that I can understand better? If you won't mind.

Thanks again.

Thread: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

Thread Tools

Display

compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

Re: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

Re: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

Re: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

Re: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

Re: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions