XLOOKUP Multiple Column Match

Hello and welcome! Today, we’re diving into XLOOKUP with multiple conditions. This topic is incredibly handy when working with datasets where you need to match data across multiple columns.

We’ll explore three different approaches: using concatenation, Boolean arrays, and the FILTER function to retrieve data based on multiple criteria. Let’s jump right in!

Video

Table of Contents

• Using Concatenation with XLOOKUP
• Using Boolean Arrays with XLOOKUP
• Returning All Matches with the FILTER Function
• Sample File
• FAQs

Exercise 1: Using Concatenation with XLOOKUP

The first method we’ll explore is using concatenation. Concatenation is just a fancy way of saying “combining multiple pieces of data into one.” This method is straightforward and works well if you want to use multiple conditions in XLOOKUP.

Our Example Data

We have the following columns:

• Invoice: The invoice number
• Customer: The customer’s code
• Item: The item code
• Amount: The total amount

Goal: Retrieve the Item and Amount into cells D6 and E6, for the given Invoice and Customer in B6 and C6:

The formula in D6 would look like this:

=XLOOKUP(B6&C6, Table1[Invoice]&Table1[Cust], Table1[[Item]:[Amount]])

Note: The ampersand (&) combines the values of our criteria.

Formula Breakdown

• B6&C6: This part concatenates (joins) the values in cells B6 and C6. For example, if B6 contains “123” and C6 contains “ABC”, this will result in “123ABC”.

• Table1[Invoice]&Table1[Cust]: This creates a new array by concatenating each entry in the Invoice column with the corresponding entry in the Cust column within Table1. It’s effectively combining these two columns into a single array of concatenated values.

• XLOOKUP(...):
  • The XLOOKUP function searches for the concatenated value from B6&C6 within the array created by Table1[Invoice]&Table1[Cust].
  • If a match is found, XLOOKUP returns the corresponding row values from Table1[[Item]:[Amount]].

• Table1[[Item]:[Amount]]: This specifies a range of multiple columns from Table1 (starting from the “Item” column to the “Amount” column). Therefore, XLOOKUP will return the matching data from all these columns for the found row.

Result: The formula returns the correct item and amount for the matching invoice and customer.

In addition to using concatenation to use multiple conditions (columns), we can also use Boolean arrays. Let’s check that option out next.

Exercise 2: Using Boolean Arrays with XLOOKUP

The second method is to use Boolean arrays in your XLOOKUP formula.

The formula would be:

=XLOOKUP(1, ((Table1[Invoice]=B6)*(Table1[Cust]=C6)), Table1[[Item]:[Amount]])

Result: Excel returns the correct row by matching where both conditions are TRUE (equal to 1).

Formula Breakdown:

Once a match is found, XLOOKUP retrieves the corresponding data from all these columns.

• (Table2[Invoice]=B6):
  • This part checks each entry in the Invoice column of Table2 to see if it matches the value in cell B6.
  • The result is an array of TRUE or FALSE values, where TRUE indicates a match and FALSE indicates no match.
• (Table2[Cust]=C6):
  • Similarly, this checks each entry in the Cust column of Table2 to see if it matches the value in cell C6.
  • The result is another array of TRUE or FALSE values.
• (Table2[Invoice]=B6) * (Table2[Cust]=C6):
  • This multiplies the two arrays of TRUE/FALSE values element-wise.
  • In Excel, TRUE is treated as 1 and FALSE as 0, so multiplying them creates an array where:
    • 1 (TRUE * TRUE) means both criteria are met (both Invoice and Cust match).
    • 0 indicates at least one criterion is not met.
• XLOOKUP(1, ...):
  • XLOOKUP searches for the first occurrence of 1 in the array, which represents the row where both conditions (Invoice = B6 and Cust = C6) are true.
• Table2[[Item]:[Amount]]:
  • This specifies the range of columns to return data from, starting from the “Item” column to the “Amount” column within Table2.

Exercise 3: Returning All Matches with the FILTER Function

The XLOOKUP function is great, but it stops at the first match it finds. What if you want to retrieve all matching rows? That’s where the FILTER function comes in handy!

How It Works

The FILTER function lets us pull all rows that meet our criteria.

The formula would be:

=FILTER(Table1[[Item]:[Amount]],((Table1[Invoice]=B6)*(Table1[Cust]=C6)))

Formula Breakdown

• Table1[[Item]:[Amount]]:
  • This defines the range from which you want to extract data. In this case, it includes all rows and columns from “Item” to “Amount” within Table1.

• (Table1[Invoice]=B6):
  • This part creates a logical array by checking each entry in the Invoice column of Table1 to see if it matches the value in cell B6.
  • The result is an array of TRUE or FALSE values where TRUE indicates that the Invoice value matches B6.

• (Table1[Cust]=C6):
  • Similarly, this checks each entry in the Cust column of Table1 to see if it matches the value in cell C6.
  • It also produces a TRUE or FALSE array.

• (Table1[Invoice]=B6) * (Table1[Cust]=C6):
  • The multiplication combines the two logical arrays. Since TRUE is treated as 1 and FALSE as 0 in Excel, multiplying them results in:
    • 1 (TRUE * TRUE) where both conditions are met (i.e., both Invoice = B6 and Cust = C6).
    • 0 where at least one condition is not met.

• FILTER(...):
  • The FILTER function uses this combined array as a filter criterion.
  • It extracts all rows from Table1[[Item]:[Amount]] where the corresponding entry in the combined array is 1 (i.e., both Invoice and Cust match the values in B6 and C6).

Result: This formula will return all rows where the Invoice and Customer match the values you’ve entered. This is super useful when you have multiple matching entries and want them all displayed.

Conclusion

And there you have it! Two effective ways to use XLOOKUP with multiple columns to return the first matching value, and how to use FILTER to retrieve all matching values.

Which method do you prefer? Let us know in the comments! Thanks for reading, and happy Excel-ing!

Sample File

To help you follow along with these exercises, I’ve included a sample file that you can download and use:

Feel free to experiment with the formulas and see how each approach works!

FAQs

1. What’s the main difference between XLOOKUP and FILTER?

• XLOOKUP stops at the first match, while FILTER returns all matching rows.

2. Why use concatenation with XLOOKUP?

• It’s a simple way to combine multiple criteria into a single lookup value.

3. Is there a way to avoid using CONCATENATE or & in the formula?

• Yes! Use the Boolean array method to achieve the same result without combining columns.

4. Can I use XLOOKUP for more than two conditions?

• Absolutely! You can add as many conditions as needed using either concatenation or Boolean arrays.

5. Does the FILTER function work with other functions?

• Yes, you can nest FILTER inside other functions, like SUM, AVERAGE, etc., to calculate values based on filtered data.

6. Can XLOOKUP handle errors gracefully?

• Yes, XLOOKUP has a built-in if_not_found argument, which allows you to display a custom message or value when no match is found.

7. Which method is faster?

• For smaller datasets, both methods are quick. However, using Boolean arrays might be more efficient for larger datasets.

8. Is FILTER available in all Excel versions?

• FILTER is available in Office 365 and Excel 2019 and later versions.