Excel should make your life easier, particularly when it comes to organizing large datasets and then finding specific bits of information within those datasets. Fortunately, the XLOOKUP function was specifically designed with this need in mind. Rather than trying to manually track down corresponding data points or combining multiple functions, you can simply use XLOOKUP and find the information you need in seconds.
But if you’ve never used XLOOKUP before, you probably have a lot of questions. For example, what is the XLOOKUP function? How does it work? What is the standard XLOOKUP formula? Finally, what is the difference between XLOOKUP and VLOOKUP?
In today’s guide, we’ll answer all of these questions and more. And remember, if you need additional help with Excel, you can always reach out to the trained IT experts at Geeker. Now let’s get started!
What Is XLOOKUP?
XLOOKUP refers to Excel’s revamping of the VLOOKUP, HLOOKUP, and INDEX-MATCH functions. While these functions are still available (and the only option for users of older versions of Excel), XLOOKUP offers a better overall way to find data within your spreadsheet. So, even if you’ve never used XLOOKUP before, there’s a good chance that you have some experience with one or more of its predecessors.
In essence, XLOOKUP enables you to search a range or array based on a specified search value and return an associated value in a different column or row. For example, let’s say that you use Excel to keep track of your company’s employee database. Each row includes data like the employee’s name, ID number, contact information (email address), and job title. XLOOKUP gives you the ability to input one of these values and find the first corresponding value for your desired search result. Perhaps you know an employee’s ID number, but you don’t know their job title, or you know their name, but you don’t have their contact information. XLOOKUP makes it easy to use one data point to find a corresponding data point.
XLOOKUP Vs VLOOKUP
Prior to the introduction of XLOOKUP by Microsoft 365 in 2019, VLOOKUP was the best function for looking up associated information in different rows. However, XLOOKUP improved upon VLOOKUP in a number of ways. More specifically, here are some of the differences between VLOOKUP and XLOOKUP:
- Lookup Direction – VLOOKUP is short for “Vertical Lookup,” and it can only look up values in columns (i.e. vertically). Alternatively, XLOOKUP can look up values in columns and rows (horizontally and vertically).
- Reference Column – VLOOKUP is limited to lookup values in the first column of the array, while XLOOKUP can search for a value in any column and return a result from any column.
- Return Values – VLOOKUP can only return values to the right of the lookup column, while XLOOKUP can return values to the left or right of the lookup column.
- Error Handling – VLOOKUP will return an error if there is not an exact match, though this can be remedied by using VLOOKUP with the IFERROR function. However, XLOOKUP allows you to specify a default return value if no match can be found.
- Lookup Type – VLOOKUP returns an approximate match search by default, whereas XLOOKUP returns an exact match search by default. However, it’s important to point out that both functions can return either type of lookup.
- Array Support – VLOOKUP can only return a single corresponding value, while XLOOKUP can return a single value, a range of values, or an array.
- Function Complexity – VLOOKUP tends to be the best option for simple vertical lookups, while XLOOKUP offers greater functionality and complexity.
- Availability – VLOOKUP is still available in all versions of Excel (as of this writing), but XLOOKUP is only available in post-2019 versions.
HLOOKUP
As you might have guessed, since there is an independent “Vertical Lookup” function, there is also an independent “Horizontal Lookup” function. HLOOKUP does everything that VLOOKUP does, but it searches by rows as opposed to columns. XLOOKUP simply works as a combination of HLOOKUP and VLOOKUP, allowing you to search by rows and columns simultaneously.
INDEX-MATCH
The INDEX and MATCH functions can be used together to return the same results as XLOOKUP. However, there are a few differences to consider. First and foremost, INDEX and MATCH are two distinct functions that must be used together, which makes them a bit more involved than XLOOKUP by default. And like VLOOKUP and HLOOKUP, INDEX and MATCH will automatically return an error if no match is found, unless you pair them with the IFERROR function. It’s also important to note that, while INDEX and MATCH can return more than single values, it is not quite as intuitive in this respect as XLOOKUP. That said, if you’re using an older version of Excel and don’t have access to XLOOKUP, the INDEX and MATCH functions might be the best alternative.
How To Use XLOOKUP
Here is the basic XLOOKUP formula:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
And here is what each of these inputs means:
- Lookup Value – The value you’re searching for.
- Lookup Array – The range or array where you’re looking for your Lookup Value.
- Return Array – The range or array from which you want a return a value when a match is found.
- (Optional) If Not Found – Specify a value to return if the Lookup Value cannot be found.
- (Optional) Match Mode – Specify match types beyond exact matches, such as wildcard matching.
- (Optional) Search Mode – Specify if the search should look through the array from first to last or last to first.
As an example, let’s say that you want to find the price of a specific product. The names of the products are in Column A, while the prices are in Column C. You have a list of products that extends to the 5th row of your spreadsheet. The name of the product you want to find is “Camera.” You would input the following formula:
=XLOOKUP(“Camera”,A1:A5,C1:C5)
Assuming the search can find “Camera” in the range A1:A5, Excel will return the corresponding price from the range C1:C5.
XLOOKUP With Multiple Criteria
Using multiple criteria (known as a nested XLOOKUP) makes the XLOOKUP function a bit more complex. By default, XLOOKUP does not include a way to search using multiple criteria, so you’ll have to get a little creative. The easiest way to do it is to create a “helper” column that combines two or more criteria. Here is an example using our previous search:
| A | B | C | D | |
| 1 | Camera | In Stock | 400 | |
| 2 | Computer | In Stock | 1000 | |
| 3 | Smartphone | In Stock | 600 | |
| 4 | Printer | In Stock | 300 | |
| 5 | Router | In Stock | 100 |
Now let’s add a helper column that combines criteria from Columns A and C into Column D:
| A | B | C | D | |
| 1 | Camera | In Stock | 400 | Camera400 |
| 2 | Computer | In Stock | 1000 | Computer1000 |
| 3 | Smartphone | In Stock | 600 | Smartphone600 |
| 4 | Printer | In Stock | 300 | Printer300 |
| 5 | Router | In Stock | 100 | Router100 |
With the helper column, you can use a concatenated lookup value that includes more than one search criteria. If you wanted to search “Camera400,” you would use this formula:
=XLOOKUP(D1,D1:D5,A1:A5)
And there you have it! Now you know how to use Excel’s XLOOKUP function!
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!