In this tutorial, you will learn how to use XLOOKUP in Excel.
The first match it discovers is what the XLOOKUP function delivers after searching a range or an array. XLOOKUP can return the closest (approximate) match if there isn’t a match.
Once ready, we’ll get started by utilizing real-world examples to show you how to use XLOOKUP in Excel.
Table of Contents
What is XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Description |
| lookup_value | The key value to look for |
| lookup_array | the range or array to search |
| return_array | The range or array to return |
| [if_not_found] | In cases when a exact match couldn’t be found, provide back the [if not found] text you gave. |
| [match_mode] | Specify the match type:0 – Exact match. If none found, return #N/A. This is the default.-1 – Exact match. If none found, return the next smaller item.1 – Exact match. If none found, return the next larger item.2 – A wildcard match where *, ?, and ~ have special meaning. |
| [search_mode] | Specify the search mode to use:1 – Perform a search starting at the first item. This is the default.-1 – Perform a reverse search starting at the last item.2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.-2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned. |
Use XLOOKUP in Excel
Before we begin we will need a group of data to be used to use XLOOKUP in Excel.
Step 1
First, you need to have a clean and tidy group of data to work with. In this example, you can use the XLOOKUP function to look for the description for Apple.
Step 2
To find the description for ‘Apple’, you can put this formula =XLOOKUP(D2,A2:A6,B2:B6).
Step 3
Once you press enter, your formula will return something like this.
Summary
That’s all there is to it. You are welcome to copy the example spreadsheet below to see how it is done. The most crucial lesson is to enjoy yourself while doing it.
In this tutorial, I covered how to use XLOOKUP in Excel. Want more?