Comparing two lists to see where they match

**Matt Harris** · 01-06-2011, 10:52 AM

Hi,

I need to compare two lists, one a reference list and one a list of collected data (plant species names). How can I compare them so that those data I have collected which are in the reference list are somehow 'flagged up' or even aligned with the corresponding name in the reference list?

If this has already been asked and answered, please point me to the thread, as I can't find any.

Thanks

**NBVC** · 01-06-2011, 10:57 AM

=Isnumber(Match(A1,B:B,0))

this will look for A1 value within column B and return True if found, False if not found.

Adjust as needed and copy down.

**Matt Harris** · 01-06-2011, 12:01 PM

Thanks for the reply. I can't get that to work, but then I don't really know how to use formulas correctly. If I am to understand that I have to go through list A (about 50 species) one at a time to see if it occurs in list B (600 sp) then that could be very tedious, and no less time-consuminag than doing it manually. I would have thought that comparing two lists would be a basic and straightforward function - it's something I need to do each day, but I know no-one who knows how to do this on excel.

**NBVC** · 01-06-2011, 12:07 PM

It is one formula that you just copy down, by dragging the little black square at bottom right corner of the cell... it adjusts to the next cell reference...

Say your list A occurs in Sheet1, cells A2:A50

and your list B is in sheet2, cells A2:A60,

Then to compare, you would put the formula in Sheet1, B2:

=ISNUMBER(MATCH(A2,Sheet2!$A$2:$A$60,0))

and copy it down, so that each item in list A is compared to the whole range in list B.