Forum Discussion

boodahbellie's avatar
boodahbellie
Copper Contributor
May 24, 2022

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 th...
excel
Formulas and Functions
Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
May 24, 2022

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.

 

  • boodahbellie's avatar
    boodahbellie
    Copper Contributor
    May 25, 2022

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

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 25, 2022

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