# Compare Two Lists Using VLOOKUP

*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.

- On the ‘
*Customer Report’*worksheet, click on a cell in the data. - Press
**CTRL+A**to select all. - In the
**Name box**, type ‘*Customer_Report’*to name the range of data. - 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])**

- On the ‘
*Invoice Report’*worksheet, click in cell E4. - 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.

- Press the
**F3**key.

- 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.

- Press
**Enter**. - 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.

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

- Press
**Enter**. - 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.

**Double-click**in cell E4 to edit the formula.- 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.

- Click at the end of the formula after the last bracket.
- Type the text you want to display for a TRUE result in quote marks, e.g., “Missing Record”.
- 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.

- Press
**Enter**. - 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.

**Double-click**in cell E4 to edit the formula.- 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.