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