How to Use Excel VLOOKUP and XLOOKUP Functions to Merge Datasets

One of the most common tasks in Excel is combining data from two different lists. Imagine you have a spreadsheet from the HR department containing Employee IDs and Names. You have a second spreadsheet from the Payroll department containing Employee IDs and Salaries. You need a single report showing Names and Salaries. You cannot simply copy and paste the columns because the lists might be sorted differently, or some employees might be missing from one list. To merge these datasets accurately, you must use a lookup function to match the data based on the shared identifier (the Employee ID).

Historically, the standard tool for this was VLOOKUP. Today, Microsoft’s modern replacement is XLOOKUP.

Part 1: The Classic Approach (VLOOKUP)

VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value in the same row from another column.

The Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The Scenario:

• Sheet 1 (Master List): Column A is Employee ID. You want the Salary in Column B.
• Sheet 2 (Payroll Data): Column A is Employee ID. Column B is Department. Column C is Salary.

To pull the salary into Sheet 1, click on cell B2 and enter:

=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)

How it works:

1. A2 (lookup_value): “Take the Employee ID in this row.”
2. Sheet2!A:C (table_array): “Search for that ID in the first column of this range (Sheet 2, Column A).”
3. 3 (col_index_num): “Once you find the ID, count over to the 3rd column in that range (Column C, Salary) and return that value.”
4. FALSE (range_lookup): “Only accept an exact match for the Employee ID. If you can’t find it, return an #N/A error.” (Almost always use FALSE).

The Flaws of VLOOKUP:
VLOOKUP has strict rules that cause immense frustration:

• It can only search left-to-right. The lookup value (Employee ID) must be in the leftmost column of the table_array. If the ID was in Column C and the Salary in Column A, VLOOKUP would fail completely.
• If someone inserts a new column into Sheet 2 (e.g., adding a “Hire Date” column between ID and Salary), the col_index_num (3) becomes incorrect, and your formula breaks.

Part 2: The Modern Approach (XLOOKUP)

Introduced in Office 365, XLOOKUP solves all of VLOOKUP’s architectural flaws. It is faster, more flexible, and less prone to breaking when spreadsheets change.

The Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Let’s solve the exact same scenario using XLOOKUP:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not Found")

How it works:

1. A2 (lookup_value): “Take the Employee ID in this row.”
2. Sheet2!A:A (lookup_array): “Search for that ID specifically in this column.”
3. Sheet2!C:C (return_array): “When you find it, return the value from the exact same row in this column.”
4. “Not Found” (if_not_found): This is a built-in error handler. Instead of returning a messy #N/A error if the ID doesn’t exist, it neatly outputs “Not Found”. (With VLOOKUP, you had to wrap the formula in a complex IFERROR function to achieve this).

Why XLOOKUP is Superior

1. It searches in any direction: Because you define the lookup_array and return_array separately, it doesn’t matter what order the columns are in. You can look up a value in Column C and return a value from Column A (right-to-left).
2. It is immune to column insertions: If a user inserts a column between A and C in Sheet 2, Excel automatically updates the formula to Sheet2!D:D. Your data remains perfectly intact.
3. Default Exact Match: Unlike VLOOKUP (which defaults to an approximate match if you forget to type FALSE, leading to disastrously wrong data), XLOOKUP defaults to an exact match.

If you have a modern version of Excel, you should completely abandon VLOOKUP and adopt XLOOKUP for all your data merging tasks.