Search code examples
excellist-comparison

Compare 2 lists in Excel ?

I have 2 columns in a spreadsheet. One column has around 26 extra rows than the other. I've been trying various formulas to highlight or somehow indicate which columns are missing from the smaller of the lists...

I tried filling a 3rd colum with this : 

=FIND(B1,A1:A1102)

which I though returned 1 if b1 was in the list a1:a1102 alas it doesn't seem to be true.

Anybody got any solutions for comparing 2 lists and isolating differences?

Thanks

Solution

  • To use MATCH, go with something like the following:

     =IFERROR(MATCH(B1,$A$1:$A$1102,0),0)

    entered into cell C1 and copied down to the end of the data in column B

    This assumes that column B contains the longer list and A the shorter, of course.

    The MATCH formula will return the row in which B1 is matched in A.