How to compare two excel sheets data using vlookup

The data values we record in the excel sheets can be related in one way or the other. We can have data in one excel file that resembles another record in a different excel sheet. We can also have one excel sheet giving more information about the other excel sheet. These excel sheets are linked in a way you can move from one excel sheet to the other under the same file.

When you have such excel sheets, you can compare their data values or content. We can compare the data values from a column in sheet 1 to the other column in sheet 2. We have more than one way to compare the data between excel sheets.

We can use the MATCH function or the VLOOKUP function. Both functions perform the same function but differ in how they operate. To use the VLOOKUP function, we have to follow some of the steps. The steps are discussed below in detail.

Step 1

From your computer, open the data records from the sheets that you wish to compare. Ensure the different sheets you are comparing are accessible by the VLOOKUP function. To ensure this, make sure the sheets are on the same excel file. If you are doing it for the first time, consider the sample below.

This is a data record from sheet 1.

This is obtained from sheet 2.

Step 2

Having the above data records, we can compare the data values from sheet 1 and sheet 2. We must specify the column or the row we are doing the comparison. The formula we use is; =VLOOKUP (A2, sheet2! $A$2: $B$6, 2, FALSE). The formula will compare the value of Mango that is on cell A2 from both sheet 1 and sheet2. Because we are comparing the data from sheet 1, it will give the value of mango from sheet2.

Suitable for users of Excel 2010, 2013, 2016, 2019, and Excel for Microsoft 365.  

OBJECTIVE

Compare two lists using the VLOOKUP function in Excel.

COMPARING LISTS WITH VLOOKUP EXPLAINED

If you want to save yourself a huge amount of time in Excel when reconciling data, it’s worth taking the time to learn how to use VLOOKUP to compare two lists or columns of data.

For example, maybe you are an accountant who keeps a record of all invoices generated by a client. The client also keeps their own lists of invoices. At the end of the year, the client sends their invoice log to their accountant for reconciliation.

In this scenario, the accountant could print out both lists and spend time with a big yellow highlighter, marking invoices that are missing from the client log. A more efficient approach would be to use VLOOKUP to run a comparison.

In the invoice report generated by the accountant on the left, invoice no, 2083 is listed. In the client payment record on the right, this invoice is missing.

COMPARE LISTS USING VLOOKUP – VIDEO TUTORIAL

THINGS TO CONSIDER

• To use VLOOKUP to compare two lists, there needs to be at least one matching piece of information for each record. In this example, both workbooks have an invoice number, a date, and an amount so that I could use any of these as my lookup value. 
• The lookup value must be unique. I’m going to use the Invoice number as there is a chance that the date or the invoice amount could be duplicated somewhere in the data.
• It’s best practice to run a check for duplicate values on the column you are using as the lookup value.
• The lookup value should be in the first column as VLOOKUP can only look up information from left to right.

USING VLOOKUP TO COMPARE TWO LISTS OR COLUMNS

We’re going to build our VLOOKUP formula in stages.

NAME THE RANGE

To make life easier, name the range of data on the ‘Customer Report’ worksheet before constructing the VLOOKUP formula. This means that you won’t need to select the cell ranges and make them absolute.

1. On the ‘Customer Report’ worksheet, click on a cell in the data.
2. Press CTRL+A to select all.
3. In the Name box, type ‘Customer_Report’ to name the range of data.
4. Press Enter.

CONSTRUCT THE VLOOKUP FORMULA

Let’s now compare the lists on both worksheets using VLOOKUP.

The VLOOKUP function has 4 arguments. The last argument is optional as it’s in square brackets.  

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

1. On the ‘Invoice Report’ worksheet, click in cell E4.
2. Type =VLOOKUP(

The first argument is lookup_value. We are using the invoice number as the lookup value.

The second argument is table_array. So, where are we looking up the lookup_value. We are looking up the invoice number in the data on the ‘Customer Report’ worksheet. We could select the cell range, but because we named the range of data, we can simply recall the name.

1. Select ‘Customer_Report’ and click OK.

The third argument is col_index_num. This is usually the column of information you want to return represented as a number—Excel numbers columns from left to right with the left-most column being number 1.

However, in this example, we aren’t looking to return a specific column. We want to compare the data and return any missing records. So, we can choose any column. To keep things consistent, we are going to choose column number 1.

The final argument is range_lookup. This is a TRUE or FALSE argument. FALSE represents an exact match of the lookup_value in the table_array, and TRUE represents an approximate match. We want to match the invoice number in the table exactly.

NOTE: TRUE and FALSE arguments in Excel can also be represented as 1 and 0 respectively.

This is our completed VLOOKUP formula.

1. Press Enter.
2. Using the fill handle, copy the formula down.

We can now see an invoice number when Excel finds a match and an #N/A error when it doesn’t. The records with #N/A next to them are missing from the ‘Customer Report.’  

TIDY UP FORMULAS WITH ERROR HANDLING

The result of the VLOOKUP does the job. We have compared both lists, and we can see which records are missing. However, our report doesn’t look particularly neat and tidy. We can improve this and make the report easier to read by adding error handling into the VLOOKUP formula.

1. Double-click in cell E4 to edit the formula.
2. Type ISNA at the beginning of the formula.
3. Add another closing bracket to the end of the formula.

1. Press Enter.
2. Use the fill handle to copy the formula down.

The ISNA function checks each cell for an #N/A error. If it finds a match, it outputs a result of TRUE. If it doesn’t, it outputs a result of FALSE.

MAKE RESULTS MEANINGFUL USING THE IF FUNCTION

Our results are now a little tidier. However, it’s still quite hard to see which records are missing. We can improve this by adding the IF function to the formula, which allows us to define the output text.  

1. Double-click in cell E4 to edit the formula.
2. Type IF at the beginning of the formula.

The IF function is a logical formula. It performs a logical test and then outputs a result depending on whether the test result is TRUE or FALSE.

The IF function has 3 arguments.

IF(logical_test, [value_if_true], [value_if_false])

In this example, the logical_test is our VLOOKUP formula. The only thing we need to define is the value_if_true and the value_if_false.

1. Click at the end of the formula after the last bracket.
2. Type the text you want to display for a TRUE result in quote marks, e.g., “Missing Record”.
3. Type the text you want to display for a FALSE result in quote marks.

NOTE: If you just want the result to be a blank cell, type double quote marks.

1. Press Enter.
2. Use the fill handle to copy the formula down.

USE CONDITIONAL FORMATTING TO HIGHLIGHT MISSING RECORDS

You can supercharge this formula, by adding conditional formatting to highlight the missing records in the table.

1. Double-click in cell E4 to edit the formula.
2. Copy the ISNA and VLOOKUP part of the formula to the clipboard (CTRL+C)

• Press ESC.
• Select the cell range A4:C50.
• From the Home tab, select Conditional Formatting.
• Select New Rule.
• Choose Use a formula to determine which cells to format from the Rule Type list.
• Paste the formula from the clipboard (CTRL+V).

NOTE: Ensure you add the equals sign at the beginning of the formula.

Our objective is for each missing record to be highlighted in the table with a background fill color. This fill color needs to run across the rows but not the columns, so we need to make the column part of cell A4 absolute.

• Click Format.
• Define how you want the missing records to be highlighted by selecting a background fill color or other font formatting options.
• Click OK twice.

For more, free Excel tutorials from Simon Sez IT, take a look at the Excel Resource Centre.

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.