Forum Discussion
Comparing two music libraries
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.
3 Replies
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.
Subodh_Tiwari_sktneerI tried using your method, but every result is NA. I attached a sample portion of my file to the original post.
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","")
