How to Use Multiple Lookup Criteria in a XLOOKUP Excel Formula

Microsoft Office’s newest iteration of Excel in Office 365 introduces “dynamic array formulas” such as FILTER, SEQUENCE, SORT, UNIQUE and XLOOKUP. XLOOKUP is a new “lookup” type formula. It is predated by the VLOOKUP/HLOOKUP, INDEX-MATCH and LOOKUP formulas. Pre-XLOOKUP, lookups with multiple criteria were complex and not user-friendly; now they’re simple and easy to do. One of the key new features XLOOKUP brings to the table is the ability to lookup using multiple criteria (without complex array formulas – arrays are now dynamic!). In this blog, we will go over how to set up an XLOOKUP formula with simple multiple lookup criteria.

The basic XLOOKUP formula is as follows:

=XLOOKUP(LOOKUP_VALUE, LOOKUP_RANGE, RETURN_RANGE)

• LOOKUP_VALUE is the value for which Excel will search
• LOOKUP_RANGE tells Excel where to look for the LOOKUP_VALUE (in the form of a range)
• RETURN_RANGE tells Excel the value to return to the formula, based on the correlating row where Excel finds the LOOKUP_VALUE in the LOOKUP_RANGE

As noted above, the LOOKUP_VALUE argument can accept multiple lookup criteria from multiple ranges (separated by the ‘&’ operator); consequently, the LOOKUP_RANGE argument can also accept multiple ranges where to respectively search for the multiple LOOKUP_VALUEs (also separated by the ‘&’ operator). Let’s illustrate through an example:

Below is a simple table which contains sales quantity data by state and clothing line. Suppose we want to look up how many socks were sold in the state of Maryland.

Our two LOOKUP_VALUEs are accordingly “Maryland” for the state column and “Socks” for the product column. Our LOOKUP_RANGEs are the “State” and “Product” columns respectively. Our RETURN_RANGE is the “Sales Quantity” column. The formula is accordingly set up as follows:

Which returns the expected result of 34,134 (quantity of sock sales in the state of Maryland):

NOTE: You can provide Excel more than two LOOKUP_VALUEs and LOOKUP_RANGEs – theoretically an unlimited number. You can also provide multiple RETURN_RANGEs.

Try using XLOOKUP in place of VLOOKUP or INDEX-MATCH – you’ll be pleasantly surprised how easy and versatile it is to use.

For additional accounting and tax assistance, contact Aprio’s Business Tax team today.