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!
