Forum Discussion

Rising Flight's avatar
Rising Flight
Brass Contributor
May 17, 2020
Solved

compare two columns

Hi All i am new to excel, i have two columns, in column A i have 500 records, in Column B i have 200 records. i want to compare Column B with Column A and pull the matching records in Column C. When...
excel
Formulas and Functions
Macros and VBA
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 17, 2020

    Rising Flight See now that I made a mistake in the formula. Sorry! It should have been:

     

    =IFERROR(VLOOKUP(B1,A:A,1,FALSE),"Not Found")

     

    But, since you are not interested to see the "Not Found", you could consider to filter these out. A manual process you probable don't want either.

    This made me re-think and I could come up with the following (not most elegant) solution:

     This formula works only if your Excel version supports the relatively new FILTER function. 

     

    =FILTER(VLOOKUP(B1:B5,A:A,1,FALSE),NOT(ISNA(VLOOKUP(B1:B5,A:A,1,FALSE))))

     

    It's demonstrated in the attached workbook. 

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 17, 2020

Rising Flight This could be, e.g. in C1

 

=IFERROR(VLOOKUP(B1,A:A,2,FALSE),"Not Found")

 

and copy it dow column B.

  • Rising Flight's avatar
    Rising Flight
    Brass Contributor
    May 17, 2020

    Thanks Riny

    In the below screenshot i have ColumnA and ColumnB and in Column C i only want to get duplicate records, so the output i want to get in Column C is SrvC,SrvB,SrvA,SrvF since these 4 records in Column B are matching with Column A

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      May 17, 2020

      Rising Flight See now that I made a mistake in the formula. Sorry! It should have been:

       

      =IFERROR(VLOOKUP(B1,A:A,1,FALSE),"Not Found")

       

      But, since you are not interested to see the "Not Found", you could consider to filter these out. A manual process you probable don't want either.

      This made me re-think and I could come up with the following (not most elegant) solution:

       This formula works only if your Excel version supports the relatively new FILTER function. 

       

      =FILTER(VLOOKUP(B1:B5,A:A,1,FALSE),NOT(ISNA(VLOOKUP(B1:B5,A:A,1,FALSE))))

       

      It's demonstrated in the attached workbook. 

Resources