Lookup multiple values at once with XLOOKUP

XLOOKUP can perform lookups in any direction (vertical, horizontal, or even diagonal) and can also perform approximate or exact matches.

To start, let’s consider a simple example where we have a table of sales data, with columns for “Product”, “Region”, “Quarter”, and “Sales”. We want to lookup the sales for a particular product, in a particular region, and in a particular quarter.

Here’s what the table looks like:

To use the XLOOKUP function to lookup sales for a particular product, region, and quarter, we’ll need to use three different lookup values, separated by commas. The syntax for XLOOKUP is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Here’s what each argument means:

• lookup_value: The value we want to lookup. In our example, this will be a combination of the product, region, and quarter.
• lookup_array: The range of cells that contains the lookup values. In our example, this will be a range that includes all three columns of the table.
• return_array: The range of cells that contains the values we want to return. In our example, this will be the “Sales” column.
• if_not_found: An optional argument that specifies what should happen if the lookup value is not found. This can be set to “0”, “1”, or “na”.
• match_mode: An optional argument that specifies whether the lookup should be an exact match or an approximate match. This can be set to “0” (exact match) or “1” (approximate match).
• search_mode: An optional argument that specifies whether the lookup should be performed from the beginning of the lookup_array or from the end. This can be set to “1” (search from end) or “2” (search from start).

Now, let’s look at how we can use XLOOKUP to lookup sales for a particular product, region, and quarter. Here’s the formula we’ll use:

=XLOOKUP(A11&"|"&B11&"|"&C11,A2:A9&"|"&B2:B9&"|"&C2:C9,D2:D9)

Here’s what each part of the formula means:

• A11&"|"&B11&"|"&C11: This concatenates the values in cells A11, B11, and C11, separated by “|”. This creates a lookup value that combines the product, region, and quarter into a single value.
• A2:A9&"|"&B2:B9&"|"&C2:C9: This concatenates the values in columns A, B, and C, separated by

“|”. This creates a lookup array that combines the product, region, and quarter into a single value for each row in the table.

• D2:D9: This is the return array, which contains the sales values for each row in the table.

The result of the XLOOKUP function will be the sales value for the combination of product, region, and quarter that we’re looking up.

Here’s an example of how we can use this formula to lookup the sales for product A, in the East region, and in Q2. We would enter these values in cells A11, B11, and C11, respectively. The formula would then return the sales value of 200.

One important thing to note about XLOOKUP is that it can handle arrays of values as well. This means that we can use it to lookup multiple values at once. For example, if we wanted to lookup sales for all products, regions, and quarters at once, we could enter a range of lookup values in cells A11:C14, like this:

We could then use the XLOOKUP function to lookup the sales values for all of these combinations of product, region, and quarter at once, like this:

=XLOOKUP(A11:A14&"|"&B11:B14&"|"&C11:C14,A2:A9&"|"&B2:B9&"|"&C2:C9,D2:D9)

This formula would return an array of sales values, which we could then paste into cells D11:D14.

Another useful feature of XLOOKUP is that it can perform approximate matches, using a technique called “binary search”. This is useful when we have a range of values and we want to find the closest match to a particular value. For example, let’s say we have a table of grades, with columns for “Score” and “Grade”, like this:

We could use XLOOKUP to lookup the grade for a particular score, like this:

=XLOOKUP(E2,A2:A6,B2:B6,1,1)

Here, the “1” as the fifth argument specifies that we want to perform an approximate match. The “1” as the sixth argument specifies that we want to search from the beginning of the lookup_array. The result of this formula would be the grade that corresponds to the closest score to the value in cell E2.

I hope this tutorial has been helpful in understanding how to use XLOOKUP in Excel. Remember that XLOOKUP is a powerful function that can be used in a variety of ways to look up values in tables. By understanding the syntax and how it works, you can use it to simplify and streamline your Excel workflows.

As with any new tool, it may take some practice to get the hang of using XLOOKUP effectively. Start with simple examples, like the ones provided in this tutorial, and gradually work your way up to more complex tasks. With time and practice, you’ll be able to use XLOOKUP to its full potential and make your Excel work faster and more efficient.