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

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.

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Compare_two_file_View_Windows.mp4″]

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.

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Compare_files_vlookup.mp4″]

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

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Compare_files_iferror.mp4″]

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.

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Compare_two_files_conditional_formatting.mp4″]

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.