Excel XLOOKUP Tutorial: Replace VLOOKUP the Right Way

XLOOKUP is the modern Excel lookup function most VLOOKUP users wish they had learned earlier. It can search one column, return a value from another column, look left or right, return multiple columns, and show a friendly message when nothing is found.

If you have ever fixed a broken VLOOKUP because the return column moved, the lookup column was not first, or a missing value produced #N/A, XLOOKUP is a cleaner replacement.

1. XLOOKUP Syntax

The basic syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Here is what each argument means:

For most everyday spreadsheets, you only need the first four arguments.

Microsoft's official XLOOKUP documentation notes that XLOOKUP is not available in Excel 2016 or Excel 2019. If you share workbooks with people on older Excel versions, keep that compatibility point in mind.

2. Basic Exact Match Example

Suppose you have this employee table:

If cell F2 contains an employee ID, this formula returns the matching department:

=XLOOKUP(F2, A2:A5, C2:C5)

If F2 contains E-318, the result is Operations.

This is the mental model:

Unlike VLOOKUP, you do not count columns. You point directly to the lookup range and the return range.

3. XLOOKUP Uses Exact Match by Default

One of the safest differences between XLOOKUP and VLOOKUP is the default match behavior.

VLOOKUP can return an approximate match if you forget the final FALSE argument. XLOOKUP uses exact match by default.

That makes XLOOKUP easier to read and less likely to return a wrong result silently.

If you need a refresher on why VLOOKUP errors happen, see the VLOOKUP troubleshooting guide.

4. Use if_not_found Instead of Showing #N/A

When a lookup value does not exist, XLOOKUP returns #N/A unless you provide the optional [if_not_found] argument.

Instead of this:

=XLOOKUP(F2, A2:A5, C2:C5)

Use this:

=XLOOKUP(F2, A2:A5, C2:C5, "Employee not found")

This is cleaner than wrapping every lookup in IFNA or IFERROR.

Use a custom message when the missing value is expected, such as a new employee ID, a product code not yet loaded, or a customer that has not ordered before. Use the raw #N/A while auditing formulas, because the error can help you spot bad source data.

For broader formula error fixes, see the Excel formula errors guide.

5. Look Left or Right Without Rearranging Columns

VLOOKUP can only look from left to right because the lookup column must be the first column in the table array. XLOOKUP does not have that limitation.

Using the same table, imagine you know the employee name in F2 and want the employee ID from the column to the left:

=XLOOKUP(F2, B2:B5, A2:A5, "Name not found")

If F2 contains Nora Patel, the formula returns E-318.

This is one of the biggest reasons to use XLOOKUP in real workbooks. You can keep your source table in a logical order instead of moving columns around just to satisfy a lookup formula.

6. Return Multiple Columns with One Formula

XLOOKUP can return more than one column when the return_array covers multiple columns.

To return both Department and Salary for an employee ID:

=XLOOKUP(F2, A2:A5, C2:D5, "Employee not found")

If F2 contains E-104, the formula spills two results:

This works especially well in Excel 365 because dynamic arrays can spill results into adjacent cells. Make sure the cells to the right of your formula are empty; otherwise Excel may show a spill error.

7. Use Match Mode Only When You Need It

For normal ID, name, SKU, or invoice lookups, leave [match_mode] blank so XLOOKUP uses exact match.

Use match mode when you intentionally want a different behavior:

Example: return the discount rate for an order amount by finding the next smaller tier:

=XLOOKUP(F2, A2:A5, B2:B5, "No tier", -1)

If F2 is 1200, the formula returns 8%.

Approximate matches depend on how your tier table is designed. If your result looks wrong, check the sort order and confirm whether you wanted next smaller or next larger.

8. Search From Bottom to Top

By default, XLOOKUP searches from the first item to the last item. If your data contains repeated values and you want the latest match, set [search_mode] to -1.

Example order table:

To return the latest amount for Northwind:

=XLOOKUP(F2, A2:A5, C2:C5, "No order found", 0, -1)

The 0 keeps exact match. The -1 tells Excel to search from the bottom of the range upward.

9. Common XLOOKUP Problems and Fixes

Most XLOOKUP problems come from mismatched data, range sizes, or blocked spill results.

Example with absolute references:

=XLOOKUP(F2, $A$2:$A$100, $C$2:$C$100, "Not found")

If your formula references move when copied, review Excel cell references before building larger lookup models.

10. XLOOKUP vs VLOOKUP: Quick Decision Table

Use this table when deciding which function to use:

For most new workbooks, XLOOKUP should be your default lookup function. Keep VLOOKUP in your toolkit for old files and compatibility requirements, but use XLOOKUP when you want formulas that are easier to read, easier to audit, and less fragile when the worksheet changes.

11. Summary

XLOOKUP improves on VLOOKUP in several practical ways:

• It uses exact match by default
• It can return a custom result when no match is found
• It can look left or right
• It can return multiple columns
• It can search from the bottom up when you need the latest match
• It avoids column index numbers, which makes formulas easier to maintain

Start with this pattern:

=XLOOKUP(value_to_find, column_to_search, column_to_return, "Not found")

Once that feels natural, add match mode and search mode only when the business case truly needs them.