The XLOOKUP function is a powerful tool in Excel that can be used to search for a specific value in a table or range of data, and then return a corresponding value from another column in the same table. In this tutorial, we will explore how to use XLOOKUP with multiple columns to search for and retrieve data from a table.
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array1, [return_array2], [match_mode], [search_mode])
Parameters: lookup_value – the value to be searched for in the lookup_array. lookup_array – the range of cells that contains the values to be searched. return_array1 – the range of cells that contains the values to be returned when a match is found. return_array2 – (optional) the range of cells that contains the secondary values to be returned when a match is found. match_mode – (optional) the type of match to be used (exact match or approximate match). Default is 0 (exact match). search_mode – (optional) the direction of the search (from top to bottom or from bottom to top). Default is 1 (from top to bottom).
Example:
Suppose we have a table of sales data for a company, with the columns “Product Name”, “Month”, “Region”, “Sales”, and “Expenses”. We want to use XLOOKUP to find the sales and expenses for a particular product in a particular region for a given month.
First, we need to decide what our lookup value will be. Let’s say we want to look up the sales and expenses for “Product A” in the “North” region for the month of “January”. We can create a separate table with these values:
| Product Name | Month | Region | Sales | Expenses |
|---|---|---|---|---|
| Product A | January | North |
Next, we will use XLOOKUP to search for the sales and expenses in the original table using the values in our new table. The formula will be as follows:
=XLOOKUP(lookup_value, lookup_array, return_array1, return_array2)
In this case, the lookup_value is the combination of “Product A”, “January”, and “North”. We will concatenate these values using the “&” operator:
=”Product A”&”January”&”North”
The lookup_array is the original table of sales data, with the columns “Product Name”, “Month”, “Region”, “Sales”, and “Expenses”. We will select the range of cells containing this data:
A2:E13
The return_array1 is the range of cells containing the sales data for the original table. We will select the “Sales” column:
D2:D13
The return_array2 is the range of cells containing the expenses data for the original table. We will select the “Expenses” column:
E2:E13
The complete formula will be as follows:
=XLOOKUP(“Product A”&”January”&”North”,A2:E13,D2:D13,E2:E13)
This formula will search for the combination of “Product A”, “January”, and “North” in the original table, and return the corresponding sales and expenses data.
Conclusion: In this tutorial, we have learned how to use XLOOKUP with multiple columns to search for and retrieve data from a table. By using XLOOKUP, we can quickly and easily search for specific values in a table, and retrieve corresponding data from multiple columns.