How to Use XLOOKUP in Excel (Step-by-Step)

Formulas & functions · Updated November 22, 2026

XLOOKUP is the modern replacement for VLOOKUP and HLOOKUP in Excel. It finds a value in a column or row and returns a matching value from any other column or row, to the left or right, without the limitations of older functions. If you have Excel 2021 or Microsoft 365, you can use it. This guide walks you through the syntax, a concrete example, common mistakes, and troubleshooting.

What You’ll Need

  • Microsoft Excel 2021, Excel for Microsoft 365, or Excel for the web (XLOOKUP is not available in Excel 2019 or earlier).
  • A worksheet with at least two columns of data: one column containing the lookup value (e.g., product IDs) and another column containing the value you want to return (e.g., prices).
  • Basic familiarity with entering formulas in Excel.

Step-by-Step

  1. Understand the syntax. The basic XLOOKUP formula is:
    =XLOOKUP(lookup_value, lookup_array, return_array)

    • lookup_value – what you want to search for (e.g., a product ID in cell D2).
    • lookup_array – the column or row where you search for that value (e.g., A:A).
    • return_array – the column or row from which you want to pull the result (e.g., B:B).
  2. Set up your data. For this example, assume you have:
    • Product IDs in column A (A2:A10).
    • Product names in column B (B2:B10).
    • Prices in column C (C2:C10).

    You want to type a product ID in cell E2 and have the price appear in F2.

  3. Write the formula. Click on cell F2 and type:
    =XLOOKUP(E2, A2:A10, C2:C10)
    Press Enter. If E2 contains a product ID that exists in A2:A10, F2 shows the corresponding price from C2:C10. For example, if A3 is “P102” and C3 is 15.50, entering “P102” in E2 returns 15.50.
  4. Handle missing values (optional). To display a friendly message when the lookup value is not found, add the fourth argument:
    =XLOOKUP(E2, A2:A10, C2:C10, "Not found")
    Now, if E2 contains a value not in A2:A10, the formula returns “Not found” instead of #N/A.
  5. Use approximate match (optional). If you need an approximate match (e.g., for tiered pricing or grades), add the fifth argument:
    =XLOOKUP(E2, A2:A10, C2:C10, "Not found", -1)
    The fifth argument can be:

    • 0 or omitted – exact match (default).
    • -1 – exact match or next smaller item (like VLOOKUP’s approximate match).
    • 1 – exact match or next larger item.
    • 2 – wildcard match (use * or ? in the lookup value).
  6. Return multiple columns (optional). To return several adjacent columns, use a range for the return_array. For example, to return both product name and price:
    =XLOOKUP(E2, A2:A10, B2:C10)
    This returns two values, but in a single cell it will spill into adjacent cells if you have the formula in a cell with empty cells to the right (dynamic array behavior). To avoid spill, you can wrap with INDEX or use separate formulas.
  7. Search from bottom (optional). Add a sixth argument to search from the last match instead of the first:
    =XLOOKUP(E2, A2:A10, C2:C10, "Not found", 0, -1)
    The sixth argument: 1 or omitted searches first-to-last; -1 searches last-to-first. Useful when you have duplicate lookup values and want the most recent entry.
  8. Use XLOOKUP with a row (horizontal). XLOOKUP works horizontally too. If your lookup values are in row 1 and return values in row 3, use:
    =XLOOKUP(E1, A1:F1, A3:F3)

Common Mistakes

  • Using the wrong lookup_array size. The lookup_array and return_array must be the same size (same number of rows or columns). If they differ, XLOOKUP returns a #VALUE! error.
  • Forgetting the exact match default. XLOOKUP defaults to exact match, so if your data has slight differences (e.g., extra spaces or text vs. number), you’ll get #N/A. Use TRIM or ensure consistent formatting.
  • Using full column references with return arrays. If you use A:A for lookup and B:B for return, that’s fine, but if you use A1:A100 and B1:B50, you’ll get an error.
  • Not handling #N/A. Without the fourth argument, missing values show #N/A. Always add a custom message if you expect missing values.
  • Confusing XLOOKUP with VLOOKUP’s approximate match. VLOOKUP’s approximate match is often used by accident; XLOOKUP’s default is exact, so you must explicitly request approximate if needed.

Tips

  • Use table references for dynamic ranges. If your data is in an Excel Table (Insert > Table), you can use structured references like =XLOOKUP([@ProductID], Table1[ProductID], Table1[Price]) – this automatically adjusts as you add rows.
  • Combine with IFERROR for cleaner output. Wrap XLOOKUP with IFERROR if you want a blank cell instead of a message:
    =IFERROR(XLOOKUP(E2, A2:A10, C2:C10), "")
  • Use XLOOKUP for two-way lookups. You can nest XLOOKUP to find a value at the intersection of a row and column. For example:
    =XLOOKUP(G2, A2:A10, XLOOKUP(H2, B1:F1, B2:F10))
    This finds the row where G2 matches A2:A10 and the column where H2 matches B1:F1, returning the intersecting value.
  • On Mac, the formula works the same. Just type the formula as shown; the only difference is keyboard shortcuts for copying/pasting, not for the function itself.
  • Use the Function Arguments dialog. Click the fx button next to the formula bar, select XLOOKUP, and fill in the fields to avoid syntax errors.

Troubleshooting

  • #NAME? error: This means your Excel version doesn’t support XLOOKUP. Check if you have Excel 2021 or Microsoft 365. If not, use VLOOKUP or INDEX/MATCH instead.
  • #VALUE! error: Usually caused by mismatched array sizes. Verify that lookup_array and return_array have the same number of rows (or columns).
  • #N/A error: The lookup value was not found. Check for typos, extra spaces, or number stored as text. Use the fourth argument to show a custom message.
  • #REF! error: The return_array is not valid, possibly because you deleted a column or used an invalid range reference.
  • Spill issues: If you’re returning multiple columns and see #SPILL!, it means the result would overwrite existing data. Clear the cells where the spill would occur, or use a single column return.

Frequently Asked Questions

Can XLOOKUP replace VLOOKUP entirely?

Yes, XLOOKUP can do everything VLOOKUP does and more, including left lookups, approximate matches, and returning multiple columns. The only reason to use VLOOKUP is for compatibility with older Excel versions.

Is XLOOKUP available in Excel 2019?

No. XLOOKUP was introduced in Excel 2021 and Microsoft 365. In Excel 2019, you must use VLOOKUP or INDEX/MATCH.

How do I use XLOOKUP with wildcards?

Set the match_mode (fifth argument) to 2, and use * (any sequence) or ? (single character) in the lookup value. For example, =XLOOKUP("A*", A2:A10, B2:B10, "Not found", 2) finds the first value starting with “A”.

Can XLOOKUP return a value from a row above the lookup value?

Yes, because the return_array can be any range, not necessarily to the right of the lookup array. For example, if IDs are in column C and names are in column A, you can use =XLOOKUP(D2, C2:C10, A2:A10).

What’s the difference between XLOOKUP and INDEX/MATCH?

XLOOKUP is simpler and more readable, and it handles errors more elegantly. INDEX/MATCH is more flexible for complex lookups (e.g., matching on multiple criteria) but requires more steps. XLOOKUP also supports approximate match and reverse search natively.

Why does my XLOOKUP return a date as a number?

Excel stores dates as serial numbers. If the return_array contains dates, format the result cell as a date (Home > Number Format > Date) to display it correctly.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.