How to Compare Two Columns in Excel Using VLOOKUP (2024)

In this article, we will show you how to use VLOOKUP in Excel to compare two columns. Simply follow the steps below.

Compare Two Columns in Excel Using VLOOKUP

To compare two columns in Excel using VLOOKUP, simply follow the process below.

1. Enter the VLOOKUP Formula in a Blank Cell

In our example, we have a list of employees IDs from cells A2 to A8, each paired with their names in cells B2 to B8. You want to find the department of each employee by comparing their Employee IDs with a second list of Employee IDs and Departments in cells D2 to E8.

Select a blank cell next to your Employee Information table. In our case, let’s choose cell C2. Type the following formula:

=VLOOKUP(A2, $D$2:$E$8, 2, FALSE)

Where:

A2 is the cell with the Employee ID in the Employee Information table.

$D$2:$E$8 is the range in the Employee Department table where the Employee IDs and Departments are located.

2 indicates that the Department is in the second column of the range.

FALSE specifies that you want an exact match.

compare two columns in excel using vlookup

2. Copy the Formula Down Using the Fill Handle

After entering the VLOOKUP formula, click on the cell containing the formula and drag the fill handle down through the cells in column C to apply the formula to other rows.

This action will copy the VLOOKUP formula, adjusting it for each row, to compare each Employee ID in column A to the Employee IDs in the Employee Department table and return the corresponding department.

3. Review the VLOOKUP Results for Accuracy

Once you have copied the formula down the column, review the results in column C. The VLOOKUP formula should display the matching department name from the Employee Department table for each Employee ID.

If no match is found, such as in Row 8, the formula will display #N/A.

compare two columns in excel and find matches using vlookup

We hope that you now have a better understanding of how to compare two columns in Excel using VLOOKUP. If you enjoyed this article, you might also like our article on how to compare two columns in Excel for missing data or our article on how to compare two tables in Excel.