Wednesday, 24 November 2010

Compare two columns in Excel

Given a list of items in column A, and a subset of this list in column B, this formula automates checking if an item is in the subset list. If the item appears in column B, enter Y, else enter N.


In this case, the full list is in column A, and the subset in column X.

MATCH($A3,X$3:X$50,0) - checks whether the value in A3 is in the list in the range X3:X50. The final 0 determines the matching 'type' parameter - MATCH finds the first value that is exactly equal to lookup_value. The lookup_array can be in any order. Return 1 if the item is found or N/A if it is not in the lookup array (column X).

ISNA() tests whether a value is N/A, and returns true or false.
Therefore, NOT(ISNA()) returns the opposite of this... any item that is found returns 1 (i.e. not N/A), and will trigger the if true condition, entering "Y" as the result, whereas a not found item will return N/A, which will cause NOT(ISNA()) to return false, therefore entering "N" as the result.