VLOOKUP To Compare Two Lists (Same or Different Sheets)

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.

Dataset - VLOOKUP To Compare Two Lists

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.

List comparison - VLOOKUP To Compare Two Lists

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

VLOOKUP formula - VLOOKUP To Compare Two Lists

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. 

VLOOKUP formula result - VLOOKUP To Compare Two Lists

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

VLOOKUP formula AutoFill - VLOOKUP To Compare Two Lists

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

ISNA-VLOOKUP formula - VLOOKUP To Compare Two Lists

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

ISNA-VLOOKUP formula result- VLOOKUP To Compare Two Lists

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

ISNA-VLOOKUP formula autofill - VLOOKUP To Compare Two Lists

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

NOT ISNA-VLOOKUP formula - VLOOKUP To Compare Two Lists

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

NOT - ISNA-VLOOKUP formula result - VLOOKUP To Compare Two Lists

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

NOT - ISNA-VLOOKUP formula result 2 - VLOOKUP To Compare Two Lists

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

NOT - ISNA-VLOOKUP formula AutoFill - VLOOKUP To Compare Two Lists

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.

IF statement - VLOOKUP To Compare Two Lists

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

IF statement formula result - VLOOKUP To Compare Two Lists

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

IF statement formula result 2 - VLOOKUP To Compare Two Lists

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

IF statement formula AutoFill - VLOOKUP To Compare Two Lists

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")

Alternative IF statement formula - VLOOKUP To Compare Two Lists

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.

Alternative IF statement formula result - VLOOKUP To Compare Two Lists

2. Lists are in Different Sheet

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

Gym 1 Sheet - VLOOKUP To Compare Two Lists

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

Gym 2 Sheet - VLOOKUP To Compare Two Lists

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")
Different Sheet formula - VLOOKUP To Compare Two Lists

 

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 (‘’).

Different Sheet formula result - VLOOKUP To Compare Two Lists

Execute the formula and you will get the result.

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

Different sheet formula Autofill - VLOOKUP To Compare Two Lists

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")

Alternative Formula - VLOOKUP To Compare Two Lists

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

Alternative Formula result - VLOOKUP To Compare Two Lists

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

Alternative Formula AutoFill - VLOOKUP To Compare Two Lists

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.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo