How to Use XLOOKUP in Excel - Practical Guide

Learn how to use XLOOKUP to search for values in a table and return corresponding results. This modern function replaces VLOOKUP with greater flexibility, supporting left-to-right searches, exact or approximate matches, and custom error handling—essential for efficient data analysis.

Why This Matters

XLOOKUP is faster and more intuitive than VLOOKUP, reducing errors and offering advanced features for professional data management. It's essential for anyone working with large datasets or complex spreadsheets.

Prerequisites

•Understanding of basic Excel functions and cell references
•Familiarity with table structures and data organization
•Access to Excel 365 or Excel 2019 (XLOOKUP availability)

Step-by-Step Instructions

Open your spreadsheet with lookup data

Prepare a table with a lookup column (e.g., Product IDs) and a return column (e.g., Prices). Ensure data is organized in adjacent columns.

Click the cell where you want the result

Select the cell where the lookup result should appear, then click the formula bar at the top.

Type the XLOOKUP formula

Enter: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Example: =XLOOKUP("Apple", A2:A10, B2:B10).

Configure match and search modes (optional)

Add parameters: match_mode (0=exact, -1=exact or next smaller, 1=exact or next larger) and search_mode (1=first to last, -1=last to first).

Press Enter and verify results

Press Enter to execute the formula, then check that the correct value is returned. Copy the formula down to apply it to other rows.

Alternative Methods

Using VLOOKUP (legacy method)

VLOOKUP searches only to the right but works in older Excel versions. Use =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) for backward compatibility.

Using INDEX-MATCH combination

Combine INDEX and MATCH for more control: =INDEX(return_array, MATCH(lookup_value, lookup_array, 0)). This offers similar flexibility to XLOOKUP.

Tips & Tricks

✓Use absolute references ($A$2:$A$10) for lookup arrays to prevent them from changing when copying formulas.
✓Set if_not_found parameter to a custom message like "Not Found" to handle missing values gracefully.
✓Search mode -1 is useful for searching in reverse order, ideal for data sorted in descending order.

Pro Tips

★XLOOKUP is case-insensitive by default; use exact match mode 0 for case-sensitive comparisons in specific scenarios.
★Combine XLOOKUP with IFERROR to create nested lookups: =IFERROR(XLOOKUP(...), XLOOKUP(...alternative...)) for fallback logic.
★Use XLOOKUP to search in unsorted data without limitations, unlike VLOOKUP which may require sorting.

Troubleshooting

XLOOKUP returns #NAME? error

This function is only available in Excel 365 and Excel 2019 or later. If using an older version, use VLOOKUP or INDEX-MATCH instead.

Formula returns #N/A or #VALUE! error

Check that your lookup_value exists in lookup_array and that array ranges are properly formatted without merged cells or hidden rows.

Result is incorrect or unexpected

Verify match_mode parameter (0 for exact match is default). Check for extra spaces in data using TRIM function and ensure data types match.

Related Excel Formulas

VLOOKUP INDEX MATCH FILTER LOOKUP

Frequently Asked Questions

What is the difference between XLOOKUP and VLOOKUP?

XLOOKUP searches in any direction (left or right) and returns exact or approximate matches, while VLOOKUP only searches right. XLOOKUP also has better error handling and is more flexible for modern spreadsheets.

Can XLOOKUP handle multiple criteria?

XLOOKUP handles single criteria by default. For multiple criteria, combine it with helper columns or use FILTER function alongside XLOOKUP in Excel 365.

Is XLOOKUP case-sensitive?

No, XLOOKUP is case-insensitive by default. Both "Apple" and "apple" will match the same cell. If you need case-sensitive matching, use workarounds with EXACT function.

What Excel versions support XLOOKUP?

XLOOKUP is available in Excel 365 (subscription) and Excel 2019 or later. It's not available in Excel 2016 or earlier versions.

How do I use XLOOKUP with dynamic arrays?

XLOOKUP naturally returns dynamic arrays in Excel 365, allowing formulas to spill results across multiple cells automatically when returning multiple values or ranges.