# Comparing two music libraries

Occasional Contributor

# 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

# Re: Comparing two music libraries

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.

# Re: Comparing two music libraries

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

# Re: Comparing two music libraries

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","")