Compare lists using a lookup formula

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Sometimes you’ll have two separate lists of data and will want to compare the values in them – see which values are common to both lists and which are only in one of them.

One way to do this is to use a lookup formula (e.g. MATCH) which is what this topic covers.

In the Topic Example Workbook we have two lists on separate sheets. We want to lookup each value in List 1 to see if the same value occurs in List 2, returning “Y” if it does and “N” if it doesn’t. We can then do the same thing in List 2, returning “Y” if the value also exists in List 1 and “N” if it doesn’t.

To do this we can use the MATCH function which looks for a value in a column and returns the row number on which it occurs. (Or alternatively it can look across a row for a value and return the column number in which it occurs).

The syntax of the MATCH Function is =MATCH(“Lookup_Value”,”Lookup_Array”,”Match_Type”)

For further information on the MATCH function see Using a INDEX/MATCH (Lookup either way)

If the MATCH function does find a match, it will return the row number but if it doesn’t it will return #N/A. We can then use the ISNA and IF functions to capture the #N/A values – if a #N/A is returned then we know the value doesn’t exist in the other list (so we return “N”) otherwise we return “Y”.

Taking List 1 first, we have all our values in Column A so in Column B we can have our lookup formula. In Cell B1 we put our column heading ‘Does Exist in List 2?’

Then in cell B2 we type our MATCH formula to look up the value in A2 against List 2. Our formula is =MATCH(A2, List 2′!A:A,0). This returns #N/A indicating that Item 1′ doesn’t exist in List 2, but if we drag down the formula all the way we can see that Item 2 does exist – the formula in cell B3 returns 2 indicating that in List 2 (Column A of the List 2′ tab) the value ‘Item 2′ exists in Row 2.

We now have a mixture of #N/As and numbers and this is sufficient for comparing the lists – any values with a #N/A doesn’t exist in List 2 and anything else does. But it’s a little untidy so we can tidy it up by replacing with “Y” and “N” values.

To do this we can use the ISNA() function to capture the #N/A values – the function returns a TRUE value if the part inside the brackets evaluates to #N/A and FALSE if it doesn’t. So =ISNA(MATCH(A2,’List 2′!A:A,0)) returns TRUE because the formula evaluates to #N/A (indicating that a corresponding match cannot be found) whereas the formula =ISNA(MATCH(A3,’List 2’!A:A,0)) returns FALSE because this formula doesn’t evaluate to #N/A (it returns 2 because the value in cell A3 matches in row 2 of List 2).

Again this is sufficient for our purposes of comparing lists because we can filter for all the TRUE values to find all the values in List 1 which don’t match in List 2, and all the FALSE values to find all the values in List 1 which do match in List 2. But it’s a little counter-intuitive having the TRUE/FALSE that way round so we can tidy it up further by using the IF function: =IF(logical test,[Value if True], [Value if False]) – in this case our logical test is whether the match function evaluates to #N/A [i.e ISNA(MATCH…))], our ‘value is true’ element is what we want to return if the ISNA evaluates to TRUE – i.e. if a match isn’t found – so we can return something like “N” to indicate it doesn’t match. The ‘value if false’ element is what we want to return if the ISNA evaluates to FALSE – i.e if a match is found – so we can return “Y” Instead.

Our completed formula in cell B2 (List 1) is therefore =IF(ISNA(MATCH(A2,’List 2′!A:A,0)),”N”,”Y”). This returns “N” indicating that the value in cell A2 (Item 1) doesn’t exist in List 2, but as we drag this down we get a mixture of “N” and “Y” values.

We can then do the same thing in List 2- comparing each value in List 2 to see if it exists in List 1.

In the Topic Example Workbook, follow the steps above to compare the two lists.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

The completed Excel file should look like the grid below

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

The following are some things to think about when comparing lists in this way:

• If you are comparing list items which have a corresponding numerical column (or if there are multiple occurrences of list items) then you can compare the total (sum) of values within the two lists by using a SUMIF formula. For example sum values in a numerical column in List 2 where the corresponding List 2 item is equal to the item in List 1.
• You can also use conditional formatting to compare lists – this will highlight values which are different or common to both lists in a format you specify.

YouTube Video – Compare Two Excel Lists to Spot the Differences