XLOOKUP with Logical Criteria: How to Use It

XLOOKUP can match logical criteria (TRUE/FALSE, greater than, less than, etc.) by using a helper column or by building a boolean array inside the formula. You enter your lookup value and the condition you want to test, and XLOOKUP returns the first match from your data. This article explains the methods, shows examples, and covers common pitfalls.

Background: What Counts as Logical Criteria?

Logical criteria are conditions that evaluate to TRUE or FALSE. In Excel, you can test things like:

• Amount greater than 100
• Date before January 1, 2025
• Status is either “Active” or “Pending”
• Score between 80 and 90

XLOOKUP itself doesn’t have a dedicated “criteria” argument like SUMIFS or COUNTIFS. Instead, you use the lookup array to contain the result of a logical test. For example, =XLOOKUP(TRUE, (B2:B100>100), A2:A100) returns the first value in A where the corresponding B is greater than 100.

How XLOOKUP Works with Boolean Logic

XLOOKUP’s first argument is the lookup value. If you set that value to TRUE (or 1), and your lookup array consists of TRUE/FALSE values from a logical expression, XLOOKUP will find the first TRUE. The formula structure is:

=XLOOKUP(TRUE, (range1>value), return_range)

For example, to find the first sales rep who exceeded a target of 500:

=XLOOKUP(TRUE, (B2:B10>500), A2:A10)

Here, (B2:B10>500) creates an array like {FALSE; TRUE; FALSE; …}. XLOOKUP scans for the first TRUE and returns the corresponding name from A2:A10.

Using a Helper Column for Clarity

While you can embed the logical test directly in the formula, a helper column often makes the spreadsheet easier to read and audit. For example, in column C you might write =B2>500 and drag down. Then your XLOOKUP becomes:

=XLOOKUP(TRUE, C2:C10, A2:A10)

This is especially useful when the criteria are complex (multiple conditions) or when you need to reuse the same logic elsewhere.

Multiple Conditions with Boolean Arithmetic

To apply more than one condition, multiply the boolean arrays together. In Excel, TRUE*TRUE = 1, and any other combination gives 0. So:

=XLOOKUP(1, (B2:B10>500)*(C2:C10="West"), A2:A10)

This returns the first name where sales exceed 500 AND the region is “West”. Note that you use 1 as the lookup value because the multiplication yields numeric 1/0, not TRUE/FALSE.

Common Problems and How to Avoid Them

Several issues trip up users when using XLOOKUP with logical criteria:

• No match found: If no row meets the condition, XLOOKUP returns #N/A. Use the fourth argument to specify a custom message, like "No match".
• Wrong data type: The lookup value must match the array’s data type. If you use TRUE, the array must contain TRUE/FALSE, not 1/0. If you use 1, the array must contain numbers. Multiplying booleans gives 1/0, so use 1.
• Array sizes must match: Ensure the lookup array and return array have the same number of rows. Otherwise, you’ll get a #VALUE! error.
• Case sensitivity: XLOOKUP is case-insensitive by default. If you need case-sensitive matching, use the fifth argument (match_mode) with 1 for exact match, but that still won’t be case-sensitive. For case-sensitive, you’d need a different approach, like using EXACT inside an array.

Alternatives: FILTER, INDEX/MATCH, and LOOKUP

While XLOOKUP is powerful, other functions may be better for certain scenarios:

• FILTER: Returns all matching rows, not just the first. Use =FILTER(A2:A10, B2:B10>500) to get a list of all reps who exceeded the target.
• INDEX/MATCH: Works in older Excel versions. The equivalent is =INDEX(A2:A10, MATCH(TRUE, B2:B10>500, 0)).
• LOOKUP: The older LOOKUP function can also handle boolean arrays, but it requires sorted data and only returns the last match in some cases. Avoid it for this purpose.

Frequently Asked Questions

Can XLOOKUP handle text criteria like “contains”?

Yes, but you need to use a function like ISNUMBER(SEARCH()) inside the logical test. For example, to find the first product whose name contains “Pro”, use =XLOOKUP(TRUE, ISNUMBER(SEARCH("Pro", A2:A10)), B2:B10).

Why does my XLOOKUP return #N/A even though a match exists?

Check that the lookup value matches the data type. If you used TRUE, make sure your logical test returns TRUE/FALSE, not 1/0. Also verify that the arrays are the same size and that there are no extra spaces or non-printable characters in text data.

How do I return the last match instead of the first?

Use the sixth argument (search_mode) of XLOOKUP. Set it to -1 to search from last to first. For example, =XLOOKUP(TRUE, (B2:B10>500), A2:A10, , , -1) returns the last matching rep.

Can I use logical criteria with XLOOKUP on a table with headers?

Yes, just reference the data range without headers, or use structured references like Table1[Column1] and Table1[Column2]. The formula works the same way.

Is XLOOKUP faster than INDEX/MATCH for large datasets?

XLOOKUP is generally efficient, but for very large arrays, boolean calculations can slow down. INDEX/MATCH with a precomputed helper column can be faster because it avoids recalculating the entire array each time. In practice, the difference is negligible for typical spreadsheet sizes.

Related

• Headphones vs Speakers: How to Choose the Right Option
• Google Wallet Features I Wish I Knew Sooner
• Patreon vs Ko-fi: Which Is Better for Creators?