XLOOKUP in Excel: Formula Examples and #N/A Fixes

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. Quick Start: Write an XLOOKUP Formula

For most everyday spreadsheets, start with this pattern:

=XLOOKUP(F2, A2:A100, C2:C100, "Not found")

Read it like this:

The most important XLOOKUP habit is to separate the search column from the return column. Unlike VLOOKUP, you do not count column numbers.

2. 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: lookup_value, lookup_array, return_array, and [if_not_found].

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.

3. 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.

4. 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. If your current problem is pulling data from another worksheet, start with VLOOKUP across sheets, then switch to XLOOKUP when your Excel version supports it.

5. 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. If your lookup should find a match but still returns #N/A, work through the common checks in How to Fix VLOOKUP #N/A in Excel; the same data issues often affect XLOOKUP too. For hidden spaces and text-number mismatches, use Excel Data Cleaning before retrying the lookup.

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

6. 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.

7. 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. For a broader explanation of spilled results, see Excel Dynamic Array Formulas.

8. Use XLOOKUP to Compare Two Columns

XLOOKUP can also help you compare two columns and mark which values are missing from another list.

Suppose column A contains product IDs from a new order file, and column D contains product IDs from your master list. To check whether A2 exists in the master list, use:

=XLOOKUP(A2, $D$2:$D$100, $D$2:$D$100, "Missing")

If the product ID exists in column D, Excel returns the matching ID. If it does not exist, Excel returns Missing.

For a cleaner found/missing label, wrap XLOOKUP in ISNA:

=IF(ISNA(XLOOKUP(A2, $D$2:$D$100, $D$2:$D$100)), "Missing", "Found")

This is useful when checking customer lists, product IDs, invoice numbers, or any two columns that should contain matching records.

For a broader set of list comparison options, including COUNTIF, visual highlighting, and FILTER, see How to Compare Two Columns in Excel.

If you need two conditions to identify one row, such as Product ID plus Region, compare the modern XLOOKUP pattern with VLOOKUP with Multiple Criteria in Excel.

9. 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.

10. 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.

11. 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.

For a focused diagnosis of #N/A, #VALUE!, #SPILL!, and copied-formula problems, see XLOOKUP troubleshooting.

12. Why XLOOKUP Is Missing in Excel

If XLOOKUP is missing from your Excel formulas, the most likely reason is Excel version support. XLOOKUP is available in Microsoft 365, Excel for the web, and newer perpetual versions such as Excel 2021 and later.

If you are using Excel 2016 or Excel 2019, XLOOKUP may not be available. In that case, use one of these options:

For a side-by-side comparison, see VLOOKUP vs XLOOKUP vs INDEX MATCH.

13. 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.

14. FAQ

How do I write an XLOOKUP formula in Excel?

Use this pattern:

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

For example, =XLOOKUP(F2, A2:A100, C2:C100, "Not found") searches for the value in F2, looks in column A, and returns the matching value from column C.

Why is XLOOKUP missing in Excel?

Your Excel version may not support it. XLOOKUP is available in Microsoft 365, Excel for the web, Excel 2021, and newer versions. For Excel 2016 or Excel 2019, use VLOOKUP or INDEX MATCH instead.

Can XLOOKUP compare two columns?

Yes. Use one column as the lookup array and the other as the list to check. For example, =XLOOKUP(A2, $D$2:$D$100, $D$2:$D$100, "Missing") checks whether the value in A2 appears in column D.

Is XLOOKUP better than VLOOKUP?

For new workbooks in modern Excel, usually yes. XLOOKUP uses exact match by default, can look left or right, can return multiple columns, and avoids fragile column index numbers. VLOOKUP is still useful for older workbooks and Excel versions that do not support XLOOKUP.

15. 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.

16. Related Guides

• Choose between one-value lookups and dynamic row reports with Excel FILTER vs VLOOKUP.
• Compare lookup formula options in VLOOKUP vs XLOOKUP vs INDEX MATCH.
• Extract matching rows with the Excel FILTER function tutorial.
• Clean imported data before lookup checks with Excel Data Cleaning.