The XLOOKUP function finds values in a range by row. You give the function a search term, and it returns a result. This function helps to bring information together from separate tables of data.

With XLOOKUP, the `return_array`

does not have to be to the right of the `lookup_array`

as with the older VLOOKUP function.

Follow the examples in this tutorial with your own copy of the Google Sheet.

Contents

## Purpose

To look up and return a value from a range.

## 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 the function doesn’t find a match.`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.

## Related Functions

VLOOKUP – Search for and returns related information by row

HLOOKUP – Search for and returns related information by column

LOOKUP – Search for and return a value from one column in another column. LOOKUP will make an approximate match and must have your data sorted.

## Video Tutorial

## Examples

### Example 1 – Simple XLOOKUP

Let’s say you have a table of `Sales Reps`

and the `Regions`

in which they work.

If you want to search for the region in which Richard works, XLOOKUP can do this for you.

`=XLOOKUP(E4,A5:A8,B5:B8)`

You specify `E4`

as the `lookup`

, which is the value you will search for in the `lookup_array`

of `A5:A8`

. XLOOKUP goes to the range of `A5:A8`

, finds the name `Richard`

and returns the corresponding value of `South`

from the `return_array`

of `B5:B8`

.

### Example 2 – Using a Not-Found Message

Not all lists contain what you’re looking for. Let’s see how XLOOKUP can handle a situation with missing data.

This lookup function has a parameter called `not-found`

that you can use to return a custom value if nothing is found. It is the fourth argument in the function, as written below.

`=XLOOKUP(E4,A5:A8,B5:B8,"Not a sales rep")`

The `not-found`

parameter is optional but can provide a useful message in your spreadsheet, especially if it is shared amongst users unfamiliar with XLOOKUP.

### Example 3 – Returning Multiple Columns

The XLOOKUP function can return more than one value. Let’s have a look at how that works.

`=XLOOKUP(F4,A5:A8,B5:C8)`

The difference in this formula from the previous examples is the expansion of the `return_array`

from `B5:B8`

to `B5:C8`

. Now the function returns the values from columns `B`

*and* `C`

.

### Example 4 – Dealing with Inexact Matches

Now let’s move into data with numbers as we have more options if we don’t find the exact match.

In this example, we want to lookup the grade letter for an `87%`

. XLOOKUP is a good fit for this operation since we can adjust the `match_mode`

and `search_mode`

parameters. These modes are optional parameters; this example is the first time we use them. Let’s take a look at the formula.

`=XLOOKUP(E4,B5:B9,A5:A9,,-1,1)`

We are using `E4`

as the `lookup`

. This cell is where the `87%`

is located.

Next is the `lookup_array`

of `B5:B9`

which is the location of the percentages that correspond to the grade letters.

Now we tell the function to look through the `return_array`

of `A5:A9`

to find the letter grades, we must specify *how* the function looks.

Since there is no letter assigned specifically to `87%`

, you want to match for *next lowest value*, which is a `match_mode`

of `-1`

.

When looking for the *next lowest value*, which is the `search_mode`

, you want the function to look from the first entry to the last. This is a `search_mode`

of `1`

.

Something interesting happened with this function. We switched the order of the lookup_array and return_array. This makes the function look to the left to matching values. This was quite difficult to do using the old VLOOKUP function.

### Example 5 – Changing the Search Order

If your `lookup_array`

has multiple matches, you have some control over which match is returned by changing the `search_mode`

.

There are two scores for `Randall`

in this list: `78`

and `82`

. We want to return the last score. To do this, we must search the list from the last item to the first. We can do this by adjusting the `search_mode`

parameter. Let’s take a look at the formula.

`=XLOOKUP(E4,A5:A8,B5:B8,,,-1)`

We start by using `E4`

as the `lookup_value`

. This cell is where the name `Randall`

is located.

Next is the `lookup_array`

of `A5:A8`

which is the location of the student’s names.

Now we tell the function to look through the `return_array`

of `B5:B8`

to find the percentages, but we tell it to search from the bottom up by using a `-1`

as the value for the `search_mode`

.

### Live Examples

Use this live sheet to get a copy of the examples used in this tutorial.

## Conclusion

Using the reference and example above, you can use XLOOKUP in your spreadsheets. You will never need to use VLOOKUP again!