This guide will discuss how to use XLOOKUP function in Google Sheets.
When we want to return a result in a specific range based on a given value, we can easily do this using the XLOOKUP function in Google Sheets.
The rules for using the XLOOKUP function in Google Sheets are the following:
- The lookup_range argument must only be a single row or single column. Moreover, the
XLOOKUPfunction does not support an array with multiple rows and columns. - The inputted result_range must be the same size as the given lookup_range. For instance, if the look_up range is a column with 5 rows, then the result_range must also have 5 rows.
- If the
XLOOKUPfunction does not find a match, it will return #N/A by default. However, we can customize what the function will return if no match is found using the missing_value argument.
Lookup functions are used to search values in a given range and return the matching value in another range. Google Sheets recently launched the XLOOKUP function, which is more enhanced and versatile than other lookup functions.
Some other examples of these functions are the LOOKUP function, the VLOOKUP function, and the HLOOKUP function.
The XLOOKUP function allows us to perform exact and approximate matching, lookups in horizontal or vertical ranges, and even wildcards for partial matching.
In this guide, we will provide a step-by-step tutorial on how to use the XLOOKUP function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.
Great! Let’s dive right in.
The Anatomy of the XLOOKUP Function
The syntax or the way we write the XLOOKUP function is as follows:
=XLOOKUP(search_key,lookup_range,result_range,[missing_value],[match_mode],[search_mode])
- = the equal sign is how we activate any function in Google Sheets.
- XLOOKUP() is our
XLOOKUPfunction. This function is used to return a value in the result_range based on the position where a match was found in the look_up range. - search_key is a required argument. This refers to the value we are searching for.
- lookup_range is another required argument. This is the range we will use to search. Additionally, the range must be a single row or column.
- result_range is also a required argument. This is the range we will return the result from. Take note that this range must have the same size as the lookup_range.
- missing_value is an optional argument. This refers to the value we want to return if no match is found. By default, the function returns #N/A.
- match_mode is also an optional argument. This refers to the manner in which to find a match for the search_key value. By default, the function will search for the exact match.
- search_mode is another optional argument. This refers to the manner in which to search through the lookup_range. By default, the function will search from the first entry to the last entry in the given range.
Match and Search Options
The XLOOKUP function has more search and match modes to control the manner in searching for the relative position of the item.
The match_mode argument allows us to decide how to find a match for the given value. Refer to the table below for the different modes.
| Match Mode Options | Match Mode Behavior |
|---|---|
| 0 | This is the default option. The function will find the exact match. |
| 1 | The function will find the exact match or the next value greater than the given search_key value. |
| -1 | The function will find the exact match of the next value that is lesser than the given search_key value. |
| 2 | The function will find a wildcard match. |
Next, the search_mode argument is used to decide how to search through the given range. The table below shows the different modes we can choose from:
| Search Mode Options | Search Mode Behavior |
|---|---|
| 1 | This is the default option. The function will search from the first entry to the last entry. |
| -1 | The function will search from the last entry to the first entry. |
| 2 | The function will perform a binary search. The range must be sorted in ascending order first. |
| -2 | The function will perform a binary search. The range must be sorted in descending order first. |
A Real Example of Using XLOOKUP Function in Google Sheets
Let’s say we have a data set containing students’ test scores in a class. Our initial data set would look like this:
The spreadsheet above shows two columns with the students’ names and their respective test scores. For example, we want to return the test score based on the student’s name.
We can easily find the student’s name and return the matching test score using the XLOOKUP function.
The basic formula we will use to perform this task is:
=XLOOKUP(E1,A2:A10,B2:B10)
The first part of the formula is our search_key argument. S0 we simply chose the cell containing the value we are looking for. Then, we selected our lookup_range, which is the range containing the names of the students.
Afterward, we selected the result_range, which is the range containing the test scores.
Essentially, the formula looks for the search_key value in the given lookup_range. Once it finds the search_key, it will return the value found in the same row in the result_range.
The data set using the basic formula would look like this:
Furthermore, we can customize the XLOOKUP formula:
=XLOOKUP(E1,A2:A10,B2:B10,"Student Not Found",0,1)
In this case, we made use of the three optional arguments. Firstly, we want the function to return “Student Not Found” when it does not find a match.
Secondly, we want the function to search for the exact match, so we inputted 0 as the match_mode. Lastly, we inputted 1 as our search_mode so the function can search from the first row to the last row.
The screenshot below is a sample situation wherein the function does not find a match.
Otherwise, the customized formula with an exact match would have a final data set like this:
You can make your own copy of the spreadsheet above using the link below.
Amazing! Now we can dive into the steps of using the XLOOKUP function in Google Sheets.
How to Use XLOOKUP Function in Google Sheets
1. First, we will create a search tab where we will type in the value we are looking for. In this example, we will look for the test score of the student “Kate”.
2. We will type in an equal sign and the XLOOKUP function to start our formula. Then, we will select the cell containing the value we are looking for. Our formula would be “=XLOOKUP(E1”.
3. Then, we will select the column containing the name of the students as our lookup_range. Thus, the formula would be “=XLOOKUP(E1,A2:A10”.
4. Next, we will also select the test score column as our result_range. Our formula will become “=XLOOKUP(E1,A2:A10,B2:B10”.
5. To customize our formula, we will input “No Student Found” as our missing_value. Lastly, we will type in “0” as our match_mode and “1” as our search_mode.
Our final formula would be “=XLOOKUP(E1,A2:A10,B2:B10,”Student Not Found”,0,1)”.
6. Finally, we will press the Enter key to return the result.
And tada! We have successfully used the XLOOKUP function in Google Sheets.
You can apply this guide whenever you need to return a value based on a lookup value. You can now use the XLOOKUP function and the various other Google Sheets formulas available to create great worksheets that work for you.
That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
