The XLOOKUP function is a powerful new addition to Excel, available in the latest versions of the software. It allows you to look up a value in a table, and return a corresponding value from another column in the same table. This can be useful for a variety of tasks, such as retrieving data from a large data set, or finding a specific value in a list.
Let’s look at an example to see how this works. Suppose we have a table of employee information, with columns for their name, department, and salary:
| Name | Department | Salary |
|---|---|---|
| Alice | Sales | 50000 |
| Bob | Marketing | 60000 |
| Charlie | IT | 70000 |
| Dave | HR | 55000 |
| Emma | Sales | 65000 |
| Frank | Marketing | 75000 |
If we want to look up the salary of a specific employee, we can use the XLOOKUP formula. For example, if we want to find the salary of Bob, we can use the following formula:
=XLOOKUP("Bob", A2:A7, C2:C7)
Here, “Bob” is the value we want to look up, A2:A7 is the range of cells that contains the names in our table, and C2:C7 is the range of cells that contains the salaries in our table. The formula will search for “Bob” in the first column of our table, and return the corresponding value from the third column of our table, which is his salary. The result should be 60000.
If the value we’re looking for isn’t found in the table, the XLOOKUP formula can return a default value instead. For example, if we try to look up the salary of Alice, who isn’t in the table, we can use the following formula:
=XLOOKUP("Alice", A2:A7, C2:C7, "Not found")
Here, “Not found” is the default value that will be returned if the lookup value isn’t found in the table. The result should be “Not found”.
We can also use XLOOKUP to perform approximate matches, which can be useful for finding values that fall within a range. For example, suppose we have a table of exchange rates, with columns for the currency, exchange rate, and date:
| Currency | Exchange Rate | Date |
|---|---|---|
| USD | 1.0 | 01/01/2022 |
| EUR | 1.2 | 01/01/2022 |
| GBP | 1.4 | 01/01/2022 |
| USD | 0.9 | 01/02/2022 |
| EUR | 1.1 | 01/02/2022 |
| GBP | 1.3 | 01/02/2022 |
If we want to look up the exchange rate for a specific currency on a specific date, we can use the following formula:
=XLOOKUP(DATE(2022, 1, 2) & " USD", E2:E7 & " " & F2:F7, G2:G7)
Here, DATE(2022, 1, 2) is the date we’re interested in, ” USD” is the currency we’re interested in, and the “&” symbol is used to concatenate the two values together. E2:E7 & ” ” & F2:F7 is the range of cells that contains the currencies and dates in our table, and G2:G7 is the range of cells that contains the exchange rates in our table. The formula will search for the concatenated value of “01/02/2022 USD” in the first column of our table, and return the corresponding value from the third column of our table, which is the exchange rate. The result should be 0.9.
Finally, we can also use XLOOKUP to perform left-to-right lookups, where the lookup value is in the same row as the return value. For example, suppose we have a table of products, with columns for their name, category, and price:
| Product | Category | Price |
|---|---|---|
| Apple | Fruit | 1.0 |
| Banana | Fruit | 1.5 |
| Carrot | Veg | 0.5 |
| Broccoli | Veg | 1.0 |
| Steak | Meat | 5.0 |
| Chicken | Meat | 3.0 |
If we want to look up the price of a specific product, we can use the following formula:
=XLOOKUP("Broccoli", B2:B7, C2:C7, 0)
Here, “Broccoli” is the value we want to look up, B2:B7 is the range of cells that contains the categories in our table, and C2:C7 is the range of cells that contains the prices in our table. The formula will search for “Broccoli” in the second column of our table, and return the corresponding value from the third column of our table, which is its price. The result should be 1.0.
That’s a brief overview of how to use the XLOOKUP formula in Excel, complete with table examples. Hopefully, this tutorial has given you a good understanding of how to use this powerful function in your own work.