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!