ExcelLookupIntermediate

XLOOKUP

Searches a range for a match and returns the corresponding value from another range

Read the syntaxReview worked examplesOpen the spreadsheet app
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

Searches a range for a match and returns the corresponding value from another range

XLOOKUP is a versatile replacement for older lookup functions like VLOOKUP and HLOOKUP, offering better performance and simpler syntax. Unlike VLOOKUP, it defaults to an exact match and allows the return array to be located to the left or above the lookup array without rearranging data. It features a built-in [ound] argument, which is a modern and more efficient alternative to wrapping the formula in IFERROR, preventing the accidental masking of legitimate syntax errors. For best results, ensure the rray and rray have identical dimensions; otherwise, a #VALUE! error will occur. It supports advanced features like wildcard matching (using * or ?) and bidirectional searching (first-to-last or last-to-first), making it ideal for finding the most recent entry in a log or handling unsorted datasets without requiring a binary sort.

Quick reference

Syntax

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

Inputs

Arguments

lookup_valueThe value to search for
Example: A2
lookup_arrayThe range to search in
Example: A:A
return_arrayThe range to return from
Example: B:B
if_not_found (optional)Value to return if no match found
Example: "Not Found"
match_mode (optional)0=exact, -1=exact or next smaller, 1=exact or next larger, 2=wildcard
Example: 0
search_mode (optional)1=first to last, -1=last to first, 2=binary ascending, -2=binary descending
Example: 1

Formula patterns

Examples

1

Basic exact match with custom error message

=XLOOKUP(A2, B2:B10, C2:C10, "not found")
exact matcherror handling
2

Approximate match for tiered commission rates

=XLOOKUP(A2, B2:B5, C2:C5, 0, -1)
approximate matchfinance
3

Search from bottom to top for latest entry

=XLOOKUP(A2, B2:B20, C2:C20, "none", 0, -1)
reverse searchdata logs
4

Wildcard search for partial text matches

=XLOOKUP(A2 & "*", B2:B10, C2:C10, "no match", 2)
wildcardspartial match

Avoid these issues

Common Errors

1

#N/A

Cause: The lookup_value cannot be found in the lookup_array and the [if_not_found] argument is omitted.

Fix: Verify the lookup value exists in the source array or provide a custom string in the fourth argument to handle missing values.

2

#VALUE!

Cause: The lookup_array and return_array have different lengths or incompatible dimensions.

Fix: Ensure both range references cover the exact same number of rows or columns (e.g., B2:B10 and C2:C10).

Platform support

Compatibility

Excel 2021+Excel-first

Available in Excel 2021+ and Microsoft 365

Source: Microsoft Support

Common questions

Frequently Asked Questions

Searches a range for a match and returns the corresponding value from another range

alue: The value to search for rray: The range to search in rray: The range to return from ound: Value to return if no match found ode: 0=exact, -1=exact or next smaller, 1=exact or next larger, 2=wildcard ode: 1=first to last, -1=last to first, 2=binary ascending, -2=binary descending

#N/A: Verify the lookup value exists in the source array or provide a custom string in the fourth argument to handle missing values. #VALUE!: Ensure both range references cover the exact same number of rows or columns (e.g., B2:B10 and C2:C10).