How to compare two lists in Excel

Published on March 1, 2018 by in Excel | General

2

Recently I got asked by a colleague to help her identify compare two lists of providers in Excel. In one list there was a list of providers available in May. In the other she had a list of providers available in January. She wanted to identify what providers were in one list but not in the other. So there was a bit of a lost/found issue

lost-2747289__480

 

 

 

 

 

In order to do that, I set up a file using the following

  1. Vlookup function combined with the iferror function.
  2. Conditional formatting to highlight the missing ones
  3. Finally, filtering to just show the ones that were missing.

Step 1- Make it easy to see both sheets

Open the file which you can download here:

Vlookup_comparison_blank_97

and set up the two sheets so you can see them side by side.

  1. View | New Window (click on this once). Note that you will now see 2 after the file name.
  2. View | Arrange All. Choose Tiled and make sure you tick the box that says “Window of Active workbook” (otherwise all the files you have opened will appear on the screen.
  3. Click on each sheet so that you see May 2015 on one side and January 2015 on the other.

 

Step 2 – Use Vlookup to identify what is missing

Create a vlookup to compare the entries using the Provider_Code. Where there is a match, we will see the provider code, where there is no match we will see the infamous N/A.

  1. Click in cell B2 in the May 2015 sheet
  2. Click on Formulas | Lookup and Reference | Vlookup
  3. Click in the Lookup Value box, then click on cell A2. Note that what appears in the box is [@[PROVIDER_CODE]]
  4. Click in Table Array. Then highlight columns A and B in the January 2015 sheet.
  5. In the Col_index_num box enter 1 (this is for column 1)
  6. In the Range Lookup box, enter False.
  7. Click OK.
  8. The formula will copy down. Note that for most of the entries you see a provider code. However for some of them there is N/A – that means they are a missing entry.

Step 3 – Using Iferror() to highlight the missing entries

  1. Click in the May 2015 sheet, click in cell B2
  2. In the formula bar, click between “=” and the V
  3. Type in iferr
  4. Iferror appears in blue underneath the function. Double click on it. =iferror( will now appear to the left of the Vlookup function)
  5. Navigate to the end of the vlookup function
  6. Enter a comma (,) .
  7. Type in “No Match” (include the quotation marks)
  8. Type in )
  9. Press Enter
  10. Note the formula copies down and you will now see No Match instead of N/A

Step 4 – To apply conditional formatting

  1. Click in the May 2015 sheet, Highlight column B
  2. Click on Home | Conditional Formatting | Highlight Cells Rules | Text that contains…
  3. Type No Match into the box.
  4. Click OK. Note that all the No Match entries appear in red.
  5. Click on the filter icon beside the heading at the top of column C and filter for No Match. If you are not sure how to filter, check out this tutorial here.

 

found

Homework

If you do the same thing in the January sheet, you should end up with 7 No Matches.

You can view a copy of the completed file here.

2 Responses to “How to compare two lists in Excel”

  1. Joe G says:

    That’s way too complicated … I simply stack the data on top of each other, then add a column and label it Compare … put “May” next to May’s data and “January” next to January’s in this new column … then do a pivot table. Put the fields to compare in the Rows and Values and my Compare field in Columns. Then simply do a +/- in the column adjacent to the pivot table, filter for all results not equal to zero and there will be all of my mismatches.

    • admin says:

      Joe, thanks a million for your suggestion. Always more than one way to do anything in Excel and appreciate you sharing the technique that works for you. Thank you.

Leave a Reply