## Purpose

## Return value

## Syntax

`=XLOOKUP(lookup,lookup_array,return_array,[not_found],[match_mode],[search_mode])`

*lookup*- The lookup value.*lookup_array*- The array or range to search.*return_array*- The array or range to return.*not_found*- [optional] Value to return if no match found.*match_mode*- [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.*search_mode*- [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

## How to use

XLOOKUP is a modern replacement for the VLOOKUP function. It is a flexible and versatile function that can be used in a wide variety of situations. XLOOKUP can find values in vertical or horizontal ranges, can perform approximate and exact matches, and supports wildcards (* ?) for partial matches. In addition, XLOOKUP can perform a reverse search and offers a super-fast binary search option when working with large datasets. Watch the video below for a short demo of XLOOKUP in action:

### Example #1 - basic exact match

By default, XLOOKUP will perform an exact match. In the example below, XLOOKUP is used to retrieve the Sales amount from column E based on an exact match on the Movie in column B. The formula in H5 is:

```
=XLOOKUP(H4,B5:B9,E5:E9)
```

More detailed explanation here.

### Example #2 - basic approximate match

To enable an approximate match, provide a value for the *match_mode* argument. In the example below, XLOOKUP is used to calculate a discount based on quantity, which requires an approximate match. The formula in F5 sets *match_mode* to -1 to enable approximate match with "exact match or next smallest" behavior:

```
=XLOOKUP(E5,B5:B9,C5:C9,,-1)
```

More detailed explanation here.

### Example #3 - multiple values

XLOOKUP can return more than one value at the same time for the same match. The example below shows how XLOOKUP can be used to return three values with a single formula. The formula in C5 is:

```
=XLOOKUP(B5,B8:B15,C8:E15)
```

Notice the return array (C8:E15) includes 3 columns: First, Last, and Department. All three values are returned and spill into the range C5:E5.

### Example #4 - two-way lookup

XLOOKUP can be used to perform a two-way lookup, by nesting one XLOOKUP inside another. In the example below, the "inner" XLOOKUP retrieves an entire row (all values for Glass), which is handed off to the "outer" XLOOKUP as the return array. The outer XLOOKUP finds the appropriate group (B) and returns the corresponding value (17.25) as the final result.

```
=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))
```

### Example #5 - not found message

When XLOOKUP can't find a match, it returns the #N/A error, like other match functions in Excel. Unlike the other match functions, XLOOKUP supports an optional argument called *not_found* that can be used to override the #N/A error when it would otherwise appear. Typical values for *not_found* might be "Not found", "No match", "No result", etc. For example, to display "Not found" when no matching movie is found, you can use a formula like this:

```
=XLOOKUP(H4,B5:B9,E5:E9,"Not found")
```

You can customize this message as you like: "No match", "Movie not found", etc.

### Example #6 - wildcard match

XLOOKUP supports wildcards to enable partial match lookups. Set the *match_mode* argument to 2 to enable wildcards in XLOOKUP. In the example below, XLOOKUP is configured to perform a "contains substring" match on the Title of the books listed in column B. The search string is entered in cell G4 and the formula in cell G6 is:

`=TRANSPOSE(XLOOKUP("*"&G4&"*",data[Title],data,,2))`

Read a complete explanation here. For a slightly simpler formula, see this page.

### Example #7 - complex criteria

With the ability to handle arrays natively, XLOOKUP can be used with complex criteria. In the example below, XLOOKUP is matching the first record where: the account begins with "x" *and* region is "east" *and* the month is not April:

```
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
```

Details: (1) simple example, (2) more complex example.

### Example #8 - Binary search

XLOOKUP has a binary search mode option that performs lookups very quickly. To enable binary search mode, data must be sorted in ascending or descending order. If values are sorted in *ascending order*, use the value 2 for *search_mode*. If values are sorted in *descending order*, use the value -2. Below is the generic syntax to enable binary search mode for an exact match lookup:

```
=XLOOKUP(A1,lookup_array,return_array,,0,2) // binary search A-Z
=XLOOKUP(A1,lookup_array,return_array,,0,-2) // binary search Z-A
```

For a more detailed example, see XLOOKUP binary search.

### Match mode options

By default, XLOOKUP will perform an *exact match*. Match behavior is controlled by an optional argument called *match_mode***, **which has the following options:

Match mode | Behavior |
---|---|

0 (default) | Exact match. Will return #N/A if no match. |

-1 | Exact match or the next smaller item. |

1 | Exact match or the next larger item. |

2 | Wildcard match (*, ?, ~) |

### Search mode options

By default, XLOOKUP will start matching from the first data value. Search behavior is controlled by an optional argument called *search_mode*, which provides the following options:

Search mode | Behavior |
---|---|

1 (default) | Search from the first value |

-1 | Search from the last value (reverse) |

2 | Binary search values sorted in ascending order |

-2 | Binary search values sorted in descending order |

Binary searches are very fast, but *data must be sorted as required*. If data is not sorted properly, a binary search can return invalid results that look perfectly normal. Detailed example here.

### XLOOKUP benefits

XLOOKUP offers several important advantages compared to VLOOKUP:

- XLOOKUP can look up data to the right or to the left of lookup values
- XLOOKUP defaults to an exact match
- XLOOKUP can work with vertical and horizontal data
- XLOOKUP can perform a reverse search (last to first)
- XLOOKUP can return entire rows or columns, not just one value

For more details see XLOOKUP vs VLOOKUP.

### Notes

- XLOOKUP can work with both vertical and horizontal arrays.
- XLOOKUP will return #N/A if the lookup value is not found.
- Like the INDEX function, XLOOKUP returns a
*reference*as a result. - The size of the
*lookup_array*must be compatible with the*return_array*, or XLOOKUP will return #VALUE! - If XLOOKUP points to an Excel Table in an
*external workbook*, the other workbook must be open or XLOOKUP will return a #REF! error.