How to Compare Two Lists in Excel Using VLOOKUP

VLOOKUP (Vertical Lookup) is a powerful function in Excel that allows you to compare two lists by searching for a specific value in one list and returning a corresponding value from another list. Here's a step-by-step guide on how to use VLOOKUP to compare two lists in Excel:

1. Prepare your data: Make sure both lists are in separate columns, and each column has a header. For better understanding, let's assume List 1 is in column A, and List 2 is in column C.
2. Create a new column next to List 1 (column B) where you want the comparison result to be displayed. Give it an appropriate header, e.g., "Match Result."
3. In the first cell of the "Match Result" column (cell B2), enter the following VLOOKUP formula:

=IFERROR(VLOOKUP(A2, C:C, 1, FALSE), "Not Found")

Here's an explanation of the formula:

• A2 is the lookup value (the first value in List 1).
• C:C is the range where you want to search for the lookup value (List 2).
• 1 indicates that you want to return the value from the first column in the range (it's the same column as List 2 in this case).
• FALSE means you're looking for an exact match.
• IFERROR is used to handle cases where the lookup value isn't found in List 2. In this case, it will return "Not Found."

4. Press Enter to apply the formula. If the value from List 1 is found in List 2, it will return the same value. If it's not found, it will display "Not Found."
5. Click on the bottom right corner of the cell with the formula (B2) and drag it down to apply the formula to the rest of the cells in the "Match Result" column.

Example

Let's say you have two lists of customer IDs, and you want to find out which IDs from List 1 are present in List 2.

List 1:

A
1 Customer ID
2 101
3 102
4 103
5 104

List 2:

C
1 Customer ID
2 102
3 105
4 103
5 107

1. Create a new column next to List 1 (column B) with the header "Match Result."
2. In cell B2, enter the VLOOKUP formula:

=IFERROR(VLOOKUP(A2, C:C, 1, FALSE), "Not Found")

3. Press Enter and apply the formula to the rest of the cells in the "Match Result" column.

The result will look like this:

A      B            C
1 Customer ID  Match Result  Customer ID
2 101          Not Found     102
3 102          102           105
4 103          103           103
5 104          Not Found     107

As you can see, the formula tells you whether each customer ID from List 1 is present in List 2 or not.