Master Excel XLOOKUP in Just 3 Minutes with 3 Real-World Examples!

In this article, I’ll be discussing one of the most powerful functions in Microsoft Excel – the XLOOKUP function. I’ll provide a detailed explanation of what XLOOKUP is, how to use it, and three real-world examples to help you understand how it works.

What is XLOOKUP?

XLOOKUP is a lookup function in Microsoft Excel that allows you to search for a specific value in a range of cells and return a corresponding value from another range of cells. XLOOKUP is a newer function introduced in Excel 2019 and Excel for Microsoft 365. The function is incredibly powerful because it can search in any direction (right to left, left to right, up and down) and can return a value based on a number of search criteria.

The XLOOKUP function has four arguments:

1. Lookup_value: The value you want to find in the lookup array.
2. Lookup_array: The range of cells in which to search for the lookup value.
3. Return_array: The range of cells from which to return the result.
4. [Match_mode]: The match mode is an optional argument that determines how the function should match the lookup value with the lookup array.

Now that we know what XLOOKUP is, let’s take a look at how to use it.

How to use XLOOKUP?

Using XLOOKUP is relatively straightforward. Here are the steps:

1. Select the cell where you want to display the result.
2. Type “=XLOOKUP(” to start the function.
3. Enter the lookup value in the first argument.
4. Enter the lookup array in the second argument.
5. Enter the return array in the third argument.
6. Press enter to display the result.

Here is an example to illustrate how to use XLOOKUP:

Suppose we have a table of sales data, and we want to look up the sales of a specific product for a specific month. We can use XLOOKUP to do this by following these steps:

1. Select the cell where you want to display the result, say cell E2.
2. Type “=XLOOKUP(” in cell E2 to start the function.
3. Enter the lookup value in the first argument, say “Product A”.
4. Enter the lookup array in the second argument, say A2:A7.
5. Enter the return array in the third argument, say D2:D7.
6. Press enter to display the result.

In this example, XLOOKUP will search for “Product A” in the range A2:A7, and return the corresponding sales value from the range D2:D7.

Now that we know how to use XLOOKUP, let’s take a look at three real-world examples to help you understand how it works.

Example 1: Using XLOOKUP to retrieve data from a table

Suppose we have a table of customer data that includes customer ID, name, and email address. We want to retrieve the email address for a specific customer ID. We can use XLOOKUP to do this by following these steps:

1. Select the cell where you want to display the result, say cell C2.
2. Type “=XLOOKUP(” in cell C2 to start the function.
3. Enter the lookup value in the first argument, say “Customer 3”.
4. Enter the lookup array in the second argument, say A2:A7 (customer ID column).
5. Enter the return array in the third argument, say C2:C7 (email address column).
6. Press enter to display the result.

In this example, XLOOKUP will search for “Customer 3” in the range A2:A7, and return the corresponding email address from the range C2:C7.

Example 2: Using XLOOKUP to assign grades based on a score

Suppose we have a list of students with their test scores, and we want to assign grades based on their scores. We can use XLOOKUP to do this by following these steps:

1. Create a table that maps score ranges to grades. For example, if the score is between 90 and 100, assign an A grade.
2. Select the cell where you want to display the result, say cell C2.
3. Type “=XLOOKUP(” in cell C2 to start the function.
4. Enter the lookup value in the first argument, say the student’s score (which is in cell B2).
5. Enter the lookup array in the second argument, say the score ranges (which are in a separate table).
6. Enter the return array in the third argument, say the grades (which are also in the separate table).
7. Enter the [match_mode] argument as “-1” to get an exact match.
8. Press enter to display the result.

In this example, XLOOKUP will search for the student’s score in the score ranges table, and return the corresponding grade.

Example 3: Using XLOOKUP to retrieve data from multiple sheets

Suppose we have a workbook with multiple sheets, each containing sales data for a different region. We want to retrieve the sales for a specific product from all the sheets. We can use XLOOKUP to do this by following these steps:

1. Select the cell where you want to display the result, say cell D2.
2. Type “=SUM(” in cell D2 to start the function.
3. Type the XLOOKUP function for the first sheet, say “=XLOOKUP(“Product A”,Sheet1!A2:A7,Sheet1!D2:D7)”.
4. Copy the formula to the right to cover all the sheets, say “=XLOOKUP(“Product A”,Sheet2!A2:A7,Sheet2!D2:D7)” for the second sheet.
5. Replace the return array in each formula with a reference to the cell where the formula is located, say “=XLOOKUP(“Product A”,Sheet1!A2:A7,D2:D7)”.
6. Press enter to display the result.

In this example, XLOOKUP will search for “Product A” in the range A2:A7 of each sheet, and return the corresponding sales value from the range D2:D7. The SUM function will then add up all the sales values to give the total sales for the product.

Conclusion

XLOOKUP is an incredibly powerful function that can save you a lot of time and effort when working with large datasets in Microsoft Excel. It allows you to search for a specific value in a range of cells and return a corresponding value from another range of cells. By understanding how to use XLOOKUP and the real-world examples provided above, you can become more efficient and productive in your Excel work.