compare multiple columns

[joharmenezes]

Hi,


I have attached a sample file http://www.filedropper.com/book1_4. I want to compare columns col 1, col 2, col 3 & col 4 of sheet 1 and 2 and get the status in sheet 1


Thanks

 

[sreekhosh]

Hi T100


Try this


=IF(AND(A2=Sheet1!A2,Sheet1!B2=Sheet2!B2,Sheet1!C2=Sheet2!C2,Sheet1!D2=Sheet2!D2),"True","False")

 

[joharmenezes]

I forgot to mention the row numbers in sheet 1 may not correspond to the row numbers in sheet 2. So row 1 in sheet 1 which has values 1,2,3,4 may not necessarily correspond to row 1 in sheet 2. So 1,2,3,4 may be present on row 4 or 5, etc in sheet 2.


I have uploaded a new sheet http://www.filedropper.com/book1_5

 

[NARAYANK991]

Hi ,


Try this formula , entered as an array formula , using CTRL SHIFT ENTER :


=IF(ISNA(VLOOKUP(A2&B2&C2&D2,Sheet2!$A$2:$A$5&Sheet2!$B$2:$B$5&Sheet2!$C$2:$C$5&Sheet2!$D$2:$D$5,1,FALSE)),"Not Present in Sheet2","Present in Sheet2")


Narayan

 

[joharmenezes]

Hi NARAYANK991, thanks for the reply.


I just want to know if this will work,


=IF(SUMPRODUCT(--(Sheet2!$A$2:$A$5=$A3),--(Sheet2!$B$2:$B$5=$B3),--(Sheet2!$C$2:$C$5=$C3),--(Sheet2!$D$2:$D$5=$D3))>=1,"present","not present")


It seems to work on my sheet but I am not 100% certain if the logic is correct. So just want ur opinion. Thanks

 

[NARAYANK991]

Hi ,


Assuming that the data in both sheets is in the range A2 through D5 , your formula will work provided the addresses are corrected as follows :


=IF(SUMPRODUCT(--(Sheet2!$A$2:$A$5=$A2)*--(Sheet2!$B$2:$B$5=$B2)*--(Sheet2!$C$2:$C$5=$C2)*--(Sheet2!$D$2:$D$5=$D2))>=1,"present","not present")


Narayan

 

[SirJB7]

Hi, T100!


Another approach. Type in E2 on worksheet Sheet1 and copy down thru E5:

=SI(ESERROR(COINCIDIR(A3&"_"&B3&"_"&C3&"_"&D3;Sheet2!$A$2:$A$5&"_"&Sheet2!$B$2:$B$5&"_"&Sheet2!$C$2:$C$5&"_"&Sheet2!$D$2:$D$5;0));"Not present in sheet 2";"Present in sheet 2") -----> =IF(ISERROR(MATCH(A2&"_"&B2&"_"&C2&"_"&D2,Sheet2!$A$2:$A$5&"_"&Sheet2!$B$2:$B$5&"_"&Sheet2!$C$2:$C$5&"_"&Sheet2!$D$2:$D$5,0)),"Not present in sheet 2","Present in sheet 2")


It's an array formula so enter it with Ctrl-Shift-Enter instead of Enter.


Regards!

 

[joharmenezes]

NARAYANK991 & SirJB7 thanks