XLOOKUP Guide: Excel's Modern Lookup Function

XLOOKUP is Microsoft's answer to the limitations of VLOOKUP and HLOOKUP. This guide covers everything you need to know — syntax, examples, and when to use XLOOKUP over older lookup functions.

What is XLOOKUP?

XLOOKUP is a versatile lookup function introduced by Microsoft as the modern replacement for both VLOOKUP and HLOOKUP. While VLOOKUP has been the go-to lookup function for decades, it carries significant limitations — it can only search the leftmost column, requires a column index number, and defaults to approximate matching. XLOOKUP eliminates all of these drawbacks.

With XLOOKUP, you specify a lookup array and a separate return array, which means you can look up values in any column and return results from any other column — including columns to the left. This simple architectural change makes XLOOKUP dramatically more flexible than VLOOKUP for real-world spreadsheet tasks.

XLOOKUP also includes built-in error handling, supports exact and approximate matching, and can search from top-to-bottom or bottom-to-top. It is a single function that replaces several older functions and their workarounds.

XLOOKUP Syntax

The full XLOOKUP syntax is:

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

Parameters explained

  • lookup_value — Required. The value you want to find. This can be a cell reference, text string, or number.
  • lookup_array — Required. The range of cells to search in. This is a single column or row.
  • return_array — Required. The range of cells to return a value from. This must be the same size as the lookup array.
  • if_not_found — Optional. The value to return when no match is found. Without this, XLOOKUP returns a #N/A error.
  • match_mode — Optional. Controls the type of matching:
    • 0 — Exact match (default)
    • -1 — Exact match or next smaller value
    • 1 — Exact match or next larger value
    • 2 — Wildcard match (supports * and ?)
  • search_mode — Optional. Controls the direction of search:
    • 1 — Search first to last (default)
    • -1 — Search last to first
    • 2 — Binary search ascending (data must be sorted)
    • -2 — Binary search descending (data must be sorted)

In practice, most XLOOKUP formulas only use the first three required parameters. The optional parameters give you fine-grained control when you need it.

XLOOKUP vs VLOOKUP — Key Advantages

XLOOKUP improves on VLOOKUP in several important ways. Here are the five key advantages:

1. Looks left — no column index needed

VLOOKUP can only search the leftmost column of a range and return a value from a column to the right. If your return column is to the left of your lookup column, VLOOKUP simply cannot do it. XLOOKUP uses separate lookup and return arrays, so the return column can be anywhere — left, right, or even in a different range entirely. This also means you never need to count column numbers, a common source of VLOOKUP errors.

2. Built-in error handling

When VLOOKUP cannot find a match, it returns a #N/A error. To handle this, you need to wrap VLOOKUP in an IFERROR or IFNA function. XLOOKUP has a built-in if_not_found parameter, so the formula stays clean and readable: 

=XLOOKUP(E1, A:A, B:B, "Not found")

3. Exact match by default

VLOOKUP defaults to approximate matching (range lookup = TRUE), which catches many users off guard and leads to incorrect results. You must remember to add FALSE as the fourth argument for exact matching. XLOOKUP defaults to exact matching, which is what you want in the vast majority of lookup scenarios.

4. Can search bottom-to-top

VLOOKUP always searches from top to bottom and returns the first match. If your data has duplicates and you need the last occurrence, you are stuck. XLOOKUP's search_mode parameter lets you search from last to first by setting it to -1, which returns the last matching entry.

5. Returns arrays

XLOOKUP can return an entire row or multiple columns of data from a single formula. If you set the return array to span multiple columns, XLOOKUP spills the results across those columns. This is extremely useful for pulling complete records from a table without writing multiple VLOOKUP formulas.

XLOOKUP Examples

Below are five practical examples that demonstrate XLOOKUP's key capabilities. Each example assumes a dataset with columns for Product ID (column A), Product Name (column B), Category (column C), and Price (column D).

Example 1: Basic product lookup

Find the price of a product by its Product ID. The lookup value is in cell F1. 

=XLOOKUP(F1, A2:A100, D2:D100)

This searches column A for the value in F1 and returns the corresponding value from column D. Unlike VLOOKUP, you do not need to specify a column index number — you simply point directly at the column you want.

Example 2: Reverse lookup (looking left)

Find the Product ID for a given Product Name. Here the lookup column (B) is to the right of the return column (A) — something VLOOKUP cannot do. 

=XLOOKUP(F1, B2:B100, A2:A100)

This searches the Product Name column and returns the Product ID from the column to the left. With VLOOKUP, you would need the more complex INDEX MATCH combination to achieve this.

Example 3: With error handling

Look up a product price and display a friendly message when the product is not found, instead of the default #N/A error. 

=XLOOKUP(F1, A2:A100, D2:D100, "Product not found")

The fourth parameter, "Product not found", is returned when no match exists. Compare this to the VLOOKUP equivalent, which would require wrapping in IFERROR: 

=IFERROR(VLOOKUP(F1, A2:D100, 4, FALSE), "Product not found")

Example 4: Approximate match for tax brackets

Suppose column A has income thresholds (0, 10000, 40000, 85000) and column B has corresponding tax rates (10%, 12%, 22%, 24%). Find the tax rate for an income in F1 using the next-smaller-value match. 

=XLOOKUP(F1, A2:A5, B2:B5, , -1)

The -1 match mode finds the largest value in column A that is less than or equal to the income in F1. For example, an income of 50000 would match the 40000 threshold and return 22%. Notice the empty fourth parameter — we skip if_not_found by leaving it blank.

Example 5: Last match (search from bottom)

When a dataset has duplicate entries and you need the most recent (last) occurrence — for example, the latest order from a customer — use the search mode parameter. 

=XLOOKUP(F1, A2:A500, D2:D500, "No orders", 0, -1)

The 0 keeps exact matching, while -1 as the search mode tells XLOOKUP to search from the last row to the first. The first match it finds (which is the last occurrence in the data) is returned.

XLOOKUP Availability

XLOOKUP is not available in every spreadsheet application. Here is the current support:

  • Microsoft Excel 365 and Excel 2021+ — Full support. XLOOKUP is available in all Excel 365 subscription plans and the one-time purchase version of Excel 2021 and later. It works on Windows, Mac, the web, and mobile.
  • Google Sheets — Limited support. Google Sheets added XLOOKUP compatibility, but it behaves slightly differently. The search_mode parameter is not supported, and some edge cases may produce different results. For Google Sheets-specific work, consider using native Google Sheets functions like FILTER or QUERY instead.
  • Apple Numbers — Not supported. Numbers does not have XLOOKUP. Use VLOOKUP or LOOKUP as alternatives in Numbers.
  • Older Excel versions (2019 and earlier) — Not supported. If your team uses Excel 2019 or earlier, you will need to use VLOOKUP or INDEX MATCH instead.

If you are unsure whether your version of Excel supports XLOOKUP, type =XLOOKUP( into a cell. If Excel shows a tooltip with the function's parameters, it is supported.

When to Use XLOOKUP vs INDEX MATCH

Before XLOOKUP existed, INDEX MATCH was the go-to solution for overcoming VLOOKUP's limitations. Now that XLOOKUP is available, which should you use?

Use XLOOKUP when:

  • Everyone on your team has Excel 365 or Excel 2021+
  • You want the simplest, most readable formula
  • You need built-in error handling without extra wrappers
  • You want to search from bottom to top
  • Readability matters more than backward compatibility

Use INDEX MATCH when:

  • Your workbook must work in Excel 2019 or earlier
  • You are sharing files with users who may not have XLOOKUP
  • You need two-dimensional lookups (INDEX with two MATCH functions)
  • You are working in Google Sheets and need full reliability

For reference, here is the same lookup written both ways:

// XLOOKUP
=XLOOKUP(F1, A2:A100, D2:D100, "Not found")

// INDEX MATCH equivalent
=IFERROR(INDEX(D2:D100, MATCH(F1, A2:A100, 0)), "Not found")

Both formulas produce identical results. XLOOKUP is more concise and readable, while INDEX MATCH has broader compatibility. In modern Excel environments, XLOOKUP is the recommended choice.

Related Guides

Skip the manual work

Describe what you need in plain English and FormulaAI generates the correct XLOOKUP formula — or any other formula — instantly.

Get the App