How to Use XLOOKUP in Excel: A Step‑by‑Step Guide

What is XLOOKUP and Why Use It?

XLOOKUP is Excel’s modern replacement for the older lookup functions — VLOOKUP, HLOOKUP, and the INDEX + MATCH combo. It was introduced in Excel 365 and Excel 2021 (or later).

Why Switch to XLOOKUP?

• Simpler syntax – Only three required arguments: lookup_value, lookup_array, and return_array.
• Built‑in error handling – The optional if_not_found argument replaces the need for IFERROR.
• Bidirectional search – You can search from the bottom up or right‑to‑left without rearranging data.
• Exact match by default – Reduces the common mistake of forgetting the fourth argument in VLOOKUP.
• Dynamic‑array friendly – Returns spill ranges automatically, making it easy to combine with FILTER, SORT, etc.

Quick example

=XLOOKUP("Apple", A2:A6, B2:B6, "Not found")

Result: 1.20 (price for “Apple”). If “Apple” is missing, the formula returns “Not found” instead of #N/A.

XLOOKUP Syntax and Parameters

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

Key points

• The three required ranges must be the same size; otherwise Excel returns #VALUE!.
• XLOOKUP works vertically and horizontally, so you can look up across rows as easily as down columns.

Google Sheets note: Sheets does not have a native XLOOKUP (as of 2024). You can mimic the behavior with INDEX + MATCH or the newer LOOKUP functions.

Setting Up a Simple Lookup Example

1. Build the sample table

Place the table in A2:C5 and give it the name Products (select the range and type Products in the Name Box).

2. Write the XLOOKUP formula

Assume E2 is where you type a Product ID and E3 should show the price:

=XLOOKUP(E2, Products[Product ID], Products[Price], "Not found")

• E2 – the ID you type.
• Products[Product ID] – column to search.
• Products[Price] – column to return.
• "Not found" – custom message for missing IDs.

In Google Sheets you must reference the ranges directly, e.g. =XLOOKUP(E2, A2:A5, C2:C5, "Not found").

3. Test it

When E2 contains 103, the formula returns $9.75. If you type 105, the custom “Not found” message appears instead of an error.

4. Why XLOOKUP beats older functions

• No column‑index number to calculate.
• Built‑in error handling removes the need for extra wrappers.

Exact vs. Approximate Matches

XLOOKUP defaults to an exact match (match_mode = 0). Change the match_mode argument for approximate lookups.

Exact match (most common)

=XLOOKUP(A2, Products[Product ID], Products[Price], "Not found")

If A2 = 102, the result is $15.49; otherwise “Not found”.

Approximate match – “next smaller”

Useful for tiered tables (e.g., discounts). Set match_mode to ‑1 to return the largest lookup value that is ≤ the searched value.

=XLOOKUP(B2, Qty[MinQty], Qty[Discount%], 0, -1)

If B2 = 32, the formula returns 10% (the discount for the 25‑unit tier).

Advanced XLOOKUP Techniques

1. Multiple criteria

Create a helper array that concatenates the criteria, then look up that array.

Goal: Sales for West + Widget.

=XLOOKUP("West"&"Widget", A2:A5&B2:B5, C2:C5, "Not found")

Result: 1100.
Tip: Wrap each part with TEXT(...,"@") if any column may contain blanks.

2. Returning multiple columns (array result)

XLOOKUP can spill a whole row or column range.

Goal: Get Salary and HireDate for Bob.

=XLOOKUP("Bob", A2:A4, C2:D4)

Spills into two adjacent cells:

In Google Sheets the same syntax works once dynamic arrays are enabled.

FAQ

What versions of Excel support XLOOKUP?

XLOOKUP is available in Excel 365 (subscription) and Excel 2021 (or later). It does not exist in Excel 2019 or earlier versions.

How does XLOOKUP differ from VLOOKUP and HLOOKUP?

XLOOKUP lets you search in any direction, return values from any column or row, and includes optional error handling and match‑mode arguments. VLOOKUP/HLOOKUP can only search forward (down or right) and require a column index number, plus a separate wrapper for error handling.

Can XLOOKUP be used in Google Sheets?

Google Sheets does not have a native XLOOKUP function (as of 2024). You can achieve similar results with INDEX + MATCH, FILTER, or the newer LOOKUP functions. The syntax shown above works only in Excel.

What if XLOOKUP returns #N/A?

#N/A appears when the lookup value isn’t found and you haven’t supplied the optional if_not_found argument. Add a fourth argument, e.g. "Not found" or 0, to replace the error with a custom result.

How to handle errors in XLOOKUP results?

Use the if_not_found argument for simple cases. For more complex handling (e.g., logging or alternative calculations), wrap XLOOKUP in IFERROR or IFNA:

=IFERROR(
    XLOOKUP(E2, Products[Product ID], Products[Price]),
    "Check ID"
)

This returns “Check ID” if any error (including #N/A or #VALUE!) occurs.

All formulas shown are fully functional in the indicated Excel versions. Adjust range references as needed for your own worksheets.