Performing a comparison between a couple of lists is one of the familiar tasks in Excel. Today we are going to show how to compare two lists using **VLOOKUP**. For this session, we are using Excel 2019, feel free to use yours.

First things first, let’s get to know about today’s practice workbook.

Here we have a dataset of equipment lists of two gyms. We will compare the lists using the **VLOOKUP **function.

Note that this is a basic dataset with dummy data. In a real-life scenario, you may encounter a much larger and complex dataset.

## Practice Workbook

You are welcome to download the practice workbook from the link below.

## Compare Two Lists with VLOOKUP

### 1. Lists are in the Same Sheet

In this section, we are going to see how to compare two lists when they are within the same worksheet.

For example, we will compare whether the equipment from *Gym 2 *is present within the *Gym 1 *equipment list or not.

Here we have introduced a column to contain the result of the comparison. Now, let’s start.

#### The VLOOKUP Formula

It’s obvious that our working formula consists of the **VLOOKUP** function. Just for a reminder (if you prefer), visit this VLOOKUP article.

Okay, let’s see the formula to compare the two lists. The formula is a very basic one,

`=VLOOKUP(E5,$B$5:$B$16,1,0))`

Here we have set the element from *Gym 2 *as *lookup_value *and the equipment from *Gym 1 *as *lookup_array*. And this will return the equipment name when it will be found within the *Gym 1 *list.* *

Write the formula for all the values from *Gym *2. You will get the result.

Here when equipment has been found within *Gym 1,* the formula returned that item. But when not found? It returned the **#N/A (Not Available) **error.

#### Error Handling

A clean sheet hardly contains any error. So we should rectify the error.

Now we need to rewrite the formula in such a way that it doesn’t return an error for a value that is not present within the comparing list. For this, we are going to use the **ISNA **function.

**ISNA **checks whether a value is **#N/A**, and returns **TRUE **or **FALSE**. To know about the function, visit this ISNA article.

We need to wrap up the **VLOOPKUP** formula we have used within **ISNA**. So the updated formula will be

`=ISNA(VLOOKUP(E5,$B$5:$B$16,1,0))`

Instead of the product name, this formula will return **FALSE **when it will find the item within the list.

To observe the change thoroughly, write the formula for the rest of the values as well.

You can see the previously **#N/A **container cells become **TRUE**. These values are not in the list so the **VLOOKUP **portion returned **#N/A **and the **ISNA **makes the final result as **TRUE**.

#### Clarify using NOT

Sometimes it might be confusing when you get **FALSE **for a value that is present within the list and **TRUE **for a value that is not in the list.

To get rid of this kind of issue we can use a function called **NOT**.

The **NOT **function returns the opposite of a given logical or Boolean value. When given **TRUE**, it returns **FALSE**. When given **FALSE**, it returns **TRUE**. For further information, visit the Microsoft Support site.

Our formula will be the following one

`=NOT(ISNA(VLOOKUP(E5,$B$5:$B$16,1,0)))`

This will return **TRUE **when the item will be found within the list.

For comparing a value that is not within the list the formula will return **FALSE**.

Write the formula for the rest of the values or exercise the **AutoFill **feature.

#### Making Things User Friendly

Mere **TRUE **and **FALSE **may not be a reader-friendly description. If you open the file a few days later or a new reader goes through the file then there may create some confusion.

To make things easily understandable we can use the **IF **function. To know about this function, visit this article, IF.

The formula will be

`=IF(NOT(ISNA(VLOOKUP(E5,$B$5:$B$16,1,0))),"Found","Not Found")`

In the condition check field of **IF **we have inserted the previously used **NOT-ISNA-VLOOKUP** formula.

And we have set “Found” and “Not Found” as the *if_true_value *and *if_false_value *respectively. * *

When the condition is **TRUE **(equipment is in the list) the formula will return “Found”.

And when the condition is **FALSE **(equipment is not in the list) the formula will return “Not Found”.

For the rest of the values write the formula or exercise the **AutoFill **feature.

If you want to use the **IF **function excluding **NOT**, then all you need to do is interchange the *if_true_value *and *if_false_value*.

`=IF(ISNA(VLOOKUP(E5,$B$5:$B$16,1,0)),"Not Found","Found")`

You might have remembered, without **NOT** the lookup formula provided **FALSE **when it found the product within the list. That’s why we have set “Found” at the *if_false_value* field and it will return the result as we desired.

### 2. Lists are in Different Sheet

The lists can be located on two different sheets like here.

Here we have *Gym 1 *equipment in **Gym 1** sheet and *Gym 2 *equipment in **Gym 2 **sheet.

No worries! It will be an easy one. All we need to provide is the sheet name before the cell reference.

`=IF(NOT(ISNA(VLOOKUP(B4,'Gym 1'!$B$4:$B$15,1,0))),"Found","Not Found")`

Here we are comparing in the **Gym 2** sheet, so we need to provide this sheet name, but the *lookup_array *value is in the separate sheet **Gym 1**. That we need to provide so that Excel can understand where to find.

After the sheet name make sure to insert an exclamatory sign (!). And if the sheet name has multiple words then put the name within a single quote (‘’).

Execute the formula and you will get the result.

For the rest of the values, write the formula or use the **AutoFill **feature.

## A Quick Alternative

In Excel, you can perform a single task in various ways. This comparison task is also not beyond that. Here is an honorable mention that you can use as an alternative to **VLOOKUP.**

Instead of **VLOOKUP,** we can use the **COUNTIF **function. To know more about the function, visit this COUNTIF article.

The formula will be

`=IF(COUNTIF($B$5:$B$16,E5)<>0,"Found","Not Found")`

Within the **COUNTIF **we have checked whether occurrences of the lookup value are not equal to 0 or not.

If not 0 (means the product presents in the list) then returns “Found”, otherwise “Not Found”.

## Conclusion

That’s all for today. We have shown how to use **VLOOKUP **to compare two lists. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other approaches that we might have missed here.