XLOOKUP
The XLOOKUP function searches a range for a specified value and returns the value from the same row in another column.
XLOOKUP(search-value, search-range, return-range, if-not-found, match-type, search-type)
search-value: The value being searched for in search-range. search-value can contain any value or a REGEX string.
search-range: The cells to search.
return-range: The cells to return.
if-not-found: An optional argument to specify the display message if a match is not found.
match-type: An optional argument that specifies the type of match to search for.
exact or next smallest (-1): If there’s no match, returns an error.
exact match (0 or omitted): If there’s no exact match, returns an error.
exact or next largest (1): If there’s no match, returns an error.
wildcard (2): *, ? and ~ have a particular meaning. REGEX can only be used in XLOOKUP if you use wildcard.
search-type: An optional argument that specifies the order in which to search the range.
Binary descending (-2): Binary search that requires range to be sorted in descending order, otherwise it returns an error.
Last to first (-1): Search the range from last to first.
First to last (1 or omitted): Search the range from first to last.
Binary ascending (2): Binary search that requires range to be sorted in ascending order, otherwise it returns an error.
Notes
If either search-range or return-range is a spanning reference (such as "B"), headers and footers are automatically ignored.
To return results from an array, use INDEX with XLOOKUP.
Example |
|---|
The table below, called Products, lists products and their attributes, like size and price: |
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | Product | Length (cm) | Width (cm) | Weight (kg) | Price |
2 | Product 1 | 16 | 17 | 10 | £82.00 |
3 | Product 2 | 16 | 20 | 18 | $77.00 |
4 | Product 3 | 11 | 11 | 15 | $88.00 |
5 | Product 4 | 15 | 16 | 20 | $63.00 |
Search with XLOOKUP |
|---|
With XLOOKUP, you can insert a formula in your spreadsheet that returns any associated value by first providing the product name, then the column with the value you want to return. For example, if you wanted to return the width of Product 1 in the table above, you could use the following formula, which returns 17cm:  In this formula, the following arguments are used:
|
Set the if-not-found string |
|---|
If you wanted to search for a specific product length and return its matching width, as well as the string to return if no match is found, you could use the following formula, which returns "No match":  In this formula, the if-not-found argument is used to perform a more specific search:
|
Find the next closest value |
|---|
XLOOKUP can also provide a broad search based on a specific value and values close to it. If you change match-type from the formula above, you can return the width that matches a length of 13cm, or the next smallest value. The formula below returns a width of 11cm:  In this formula, the arguments are the same as above, except a different value is used for match-type to change how the table is searched:
|
Change the search order |
|---|
In some instances, it may be useful to change the order in which a table is searched with XLOOKUP. For example, in the table above, there are two products with a length of 16cm, so there are two potential matches if you search for 16cm in the Length column using search-value and search-range. You can set the search order using a formula like this, which returns 20cm:  In this formula, the search-type argument is used to set the order in which XLOOKUP searches the table for a match:
|
Use XLOOKUP with other functions |
|---|
XLOOKUP can also be used with other functions, like SUM. For example, you can use a formula like the one below to return £247, the SUM of the prices of Products 1, 2 and 3:  In this example, the first XLOOKUP searches for the price of Product 1, and the second XLOOKUP searches for the price of Product 3. The colon (:) between the XLOOKUP functions indicates that SUM should return not just the total price of Product 1 and Product 3, but also any values in between. |
In the formula below, XLOOKUP is used with REGEX to return Product 2, the first product with a width that starts with "2":  In this example, "wildcard (2)" is used for match-type to utilise the wildcards in the REGEX function. |
Additional examples |
|---|
Given the following table: |
A | B | C | |
|---|---|---|---|
1 | Name | Age | Salary |
2 | Amy | 35 | 71000 |
3 | Matthew | 27 | 81000 |
4 | Chloe | 42 | 86000 |
5 | Sophia | 51 | 66000 |
6 | Kenneth | 28 | 52000 |
7 | Tom | 49 | 62000 |
8 | Aaron | 63 | 89000 |
9 | Mary | 22 | 34000 |
10 | Alice | 29 | 52000 |
11 | Brian | 35 | 52500 |
=XLOOKUP(49,B2:B11,C2:C11) returns "62000", which is the salary of the first employee whose age is 49. =XLOOKUP(60000,C2:C11,B2:B11,"No match") returns "No match", as there is no employee whose salary is $60,000. =XLOOKUP(REGEX("^C.*"), A2:A11, B2:B11, FALSE, 2) returns "42", the age of "Chloe", the first employee in the range whose name starts with "C". =INDEX(XLOOKUP(A2,A2:A11,B2:C11),2) returns 71000, the second value in the array returned by XLOOKUP. |