Comparing two lists in excel

Posted by Denis on May 11, 2001 1:31 AM

Hi
I have 2 columns of which the first contains all my data range (2000 unique member numbers) and the second list contains a selection of those same numbers.
I am trying to write a formula that will return a 1 if the member number is in both columns and a zero if the member number is just in the first.
Your help will be much appreciated.
Many thanks
Denis

Posted by Aladin Akyurek on May 11, 2001 1:38 AM

Assuming that the first list is in A from A1 on, the second list in B from B1 on.

Select all the cells containing 2000 unique member numbers and give it a name, say DATA, via the Name Box or via the option Insert|Name|Define.

Use the following formula in C1 and copy down as far as needed:

=IF(ISNUMBER(MATCH(B1,DATA,0)),1,0)

Aladin