XLOOKUP

Searches a range or array for a match and returns the corresponding item from a second range or array. Supports exact and approximate matching, wildcards, and reverse search.

Syntax

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

Arguments

Argument	Description	Required
lookup_value	The value to search for	Required
lookup_array	The array or range to search in	Required
return_array	The array or range to return from	Required
if_not_found	Value to return if no match is found. Defaults to error.	Optional
match_mode	0=exact match (default), -1=exact or next smaller, 1=exact or next larger, 2=wildcard	Optional
search_mode	1=first to last (default), -1=last to first, 2=binary ascending, -2=binary descending	Optional

Examples

Basic exact match lookup

Lookup with custom not found message

Approximate match (next larger)

Related Functions

Other Data functions:

• CHOOSE - Uses index to return a value from the list of value arguments
• COLUMN - Returns the column number of the current cell context.
• DROP - Drops the first N rows and optionally first N columns from an array. Returns an error if the result would be empty.
• FILTER - Returns a filtered array containing only rows that match the criteria
• GROUPBY - Groups data by a key and applies an aggregator function to each group
• HLOOKUP - Returns the corresponding value from the specified row of table which matches exactly or approximately to the first row of the table.
• HSTACK - Horizontally stacks arrays by appending columns
• INDEX - Returns a cell value from a list or table based on its column and row numbers.
• LAST - Returns the last N rows and optionally last N columns from an array. Returns an error if the result would be empty.
• MAP - Processes each row in an array with a formula including a COL or CELL keyword and returns it as a 2D array.
• MATCH - Returns the position of the item/lookup_value in the lookup_array or range.
• PICK - Selects specific columns from an array using column numbers or header names
• ROW - Returns the 1-indexed row number of the current cell
• SEQUENCE - Returns the list of generated sequential numbers in an array.
• SORT - Sorts the values in a range or array.
• SORTBY - Sorts a range or array based on values in one or more corresponding ranges or arrays.
• TAKE - Returns the first N rows and optionally first N columns from an array. Returns an error if the result would be empty.
• TRANSPOSE - Returns the result after transposing the rows and columns of an array or range.
• UNIQUE - Returns a list of unique values in a list or range.
• VLOOKUP - Returns a value from a table by looking up a value in the first column and returning a value from the specified column
• VSTACK - Vertically stacks arrays by appending rows

---

← Back to Formula Reference