Comparing two music libraries

[boodahbellie]

I have an Excel 365 file with 2 sheets. Each sheet consists of info from 2 music libraries on 2 computers. They are listed in tables containing 3 columns (Artist, Track, Album). I want to check if the list on sheet 2 is missing any of the songs from sheet 1. I have tried XLOOKUP, MATCH, and VLOOKUP, unsuccessfully. I know that one of these, in conjunction with IF or some other function, will do the trick, but I can't quite get there.

[#2]

@boodahbellie 

One way is this...

On Sheet1,

In D2

=IF(ISNA(MATCH(A2&B2&C2,INDEX(Sheet2!$A$2:$A$500&Sheet2!$B$2:$B$500&Sheet2!$C$2:$C$500,),0)),"Missing","")

Change the 500 with the last row of data on sheet2 in the above formula but avoid referring to the whole column like $A:$A.

[#3]

@Subodh_Tiwari_sktneerI tried using your method, but every result is NA. I attached a sample portion of my file to the original post.

[#4]

@boodahbellie 

The problem is that many of the values on the Remote sheet have spaces before and/or after them, but the corresponding values on the Local sheet don't. So they don't match exactly.

You could use

=IF(ISNA(MATCH(TRIM(A3)&TRIM(B3)&TRIM(C3),INDEX(TRIM(Local!$A$2:$A$100)&TRIM(Local!$B$2:$B$100)&TRIM(Local!$C$2:$C$100),),0)),"Missing","")

and

=IF(ISNA(MATCH(TRIM(A2)&TRIM(B2)&TRIM(C2),INDEX(TRIM(Remote!$A$2:$A$100)&TRIM(Remote!$B$2:$B$100)&TRIM(Remote!$C$2:$C$100),),0)),"Missing","")