XLOOKUP With Multiple Criteria: Formulas and Examples

Formulas & functions · Updated November 21, 2026

To look up a row that meets multiple criteria in Excel, multiply the criteria tests inside XLOOKUP: =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:C100,"Not found"). This returns the value from column C for the first row where column A matches H2 and column B matches H3; the sections below explain how to adapt it for OR conditions, partial matches, duplicate results, and common errors.

What XLOOKUP with multiple criteria means

A standard XLOOKUP searches one lookup array for one lookup value. For example, it can find a product name in one column and return its price from another. Multiple criteria mean that a row must satisfy two or more tests at the same time, such as matching both a product and a region.

XLOOKUP does not have separate lookup-value and lookup-array arguments for each criterion. Instead, create a combined TRUE/FALSE test for each row and use that result as the lookup array. Multiplying Boolean tests converts TRUE to 1 and FALSE to 0. A row that passes every test has a product of 1, which XLOOKUP can find.

This approach works in Microsoft Excel for Microsoft 365 on Windows and in Excel 2021 or later. The same formula logic is available in supported Excel for Mac and Excel for the web versions. XLOOKUP is not available in Excel 2019 or earlier perpetual versions; in those editions, use a compatible alternative such as INDEX and MATCH.

Build the basic AND formula

Assume a small sales table has product names in A2:A5, regions in B2:B5, and prices in C2:C5. The requested product is in H2 and the requested region is in H3. The entries are Widget/East/125, Widget/West/140, Cable/East/35, and Widget/East/132. With those ranges and criteria cells, use:

=XLOOKUP(1,(A2:A5=H2)*(B2:B5=H3),C2:C5,"Not found")

The result is 125: the first row matches both Widget and East. The later Widget/East row contains 132, but XLOOKUP searches from the first item by default, so it returns the first matching row. Replace the example ranges with the actual columns in your sheet, keeping the lookup arrays the same size as the return array.

For three criteria, multiply another test into the lookup array. For example, with product names in A2:A100, regions in B2:B100, years in C2:C100, return amounts in D2:D100, and the requested product, region, and year in H2:H4, use:

=XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3)*(C2:C100=H4),D2:D100,"Not found")

Every condition must be true for the product to equal 1. If even one condition is false, the product is 0, so that row is not selected. This pattern scales to more criteria, although a very long formula can be harder to maintain; clear headers and separate criteria cells make it easier to audit.

Use OR logic instead of requiring every criterion

Multiplication represents AND logic: all tests must match. For OR logic, add the Boolean tests and look for a positive result. With product names in A2:A5, regions in B2:B5, return values in C2:C5, and H2 containing a product to match and H3 a region to match, this returns the first row matching either condition:

=XLOOKUP(TRUE,((A2:A5=H2)+(B2:B5=H3))>0,C2:C5,"Not found")

Adding the tests produces 1 when one condition is true and 2 when both are true. The comparison with zero turns either result into TRUE, which XLOOKUP searches for. Use this carefully: OR logic can return a row that meets only one condition, even if the other criterion seems important. If your intended rule is “this product in this region,” use multiplication, not addition.

Return the last matching row

By default, XLOOKUP searches from the beginning of the lookup array. If records are ordered by date and you want the most recent matching record at the bottom of the list, set the search mode argument to -1. With product names in A2:A5, regions in B2:B5, return values in C2:C5, and criteria in H2 and H3, use:

=XLOOKUP(1,(A2:A5=H2)*(B2:B5=H3),C2:C5,"Not found",0,-1)

In the example table, that returns 132, the last Widget/East entry. The fifth argument, 0, specifies exact matching; exact matching is XLOOKUP’s default, but including it can make the intention explicit. The sixth argument, -1, searches from last to first. This returns the last match in the current row order, not necessarily the chronologically newest item unless the data is sorted or otherwise arranged to make that true.

Handle partial and case-sensitive criteria

For a criterion that should match text contained within a cell, combine the exact test for one field with SEARCH for the text field. Suppose departments are in A2:A100, descriptions in B2:B100, results in C2:C100, the department criterion is in H2, and the phrase to find is in H3. Use:

=XLOOKUP(1,(A2:A100=H2)*ISNUMBER(SEARCH(H3,B2:B100)),C2:C100,"Not found")

SEARCH returns a number when it finds the phrase and an error when it does not; ISNUMBER converts that result to TRUE or FALSE for the multiple-criteria test. SEARCH is not case-sensitive. It can also treat the asterisk and question mark as wildcard characters in the search text, so use a different approach if those characters must be treated literally.

Ordinary equality comparisons in Excel are also not case-sensitive. If capitalization must distinguish values, use EXACT for the text test. With codes in A2:A100, departments in B2:B100, returned data in C2:C100, and the two criteria in H2 and H3, use:

=XLOOKUP(1,EXACT(A2:A100,H2)*(B2:B100=H3),C2:C100,"Not found")

EXACT checks case as well as text content. For example, it treats “ab12” and “AB12” as different. Case-sensitive matching is useful for certain identifiers, but it can also make a lookup fail when capitalization differs unexpectedly.

Choose between Boolean tests and a helper key

The Boolean-array method is usually the clearest option when criteria are stored in separate columns. It avoids changing the source table and makes each matching condition visible in the formula. It is also straightforward to add a condition by multiplying another comparison into the lookup array.

A helper key is another option: combine the fields in a spare column, then look up the combined value. With product names in A2:A100, regions in B2:B100, a helper key in D2:D100, return values in C2:C100, and criteria in H2 and H3, put this in D2 and fill it down:

=A2&"|"&B2

Then, with the same data and criteria cells, use:

=XLOOKUP(H2&"|"&H3,D2:D100,C2:C100,"Not found")

The delimiter reduces the risk of ambiguous combinations, but it does not eliminate it: if a field can contain the delimiter, two different pairs could produce the same key. Choose a character that cannot occur in the data, or use a more controlled key design. A helper key can be convenient when many formulas need the same combined lookup, but it adds a column to maintain. For a one-off lookup, the Boolean tests are often simpler.

Common errors and ways to prevent them

When a multi-criteria lookup returns “Not found” or an unexpected value, check the formula and the data before changing the lookup logic. Common causes include:

  • Unequal range sizes: Each criteria range and the return range should cover corresponding rows. Mixing A2:A100 with B2:B99 can cause an array-size error or incorrect logic.
  • Extra spaces or inconsistent data: “East” and “East ” are different text values. Clean or standardize imported data if it contains leading or trailing spaces.
  • Numbers stored as text: A numeric 2025 and text “2025” may not compare as expected. Check the source format and make both the criteria and data consistent.
  • Duplicate matches: XLOOKUP returns one result, not every matching row. By default it returns the first; use reverse search for the last.
  • Blank cells: A blank criterion can match blank entries in a field. If blank criteria should mean “ignore this condition,” the formula needs logic that handles that rule explicitly.
  • Incorrect AND/OR logic: Multiplication requires all conditions; addition followed by a greater-than-zero test accepts any condition. Confirm the business rule before choosing.

The fourth XLOOKUP argument controls the message shown when there is no match. In the examples, “Not found” is text chosen for clarity; it can be replaced with another message or an empty string. An empty result can look like a successful blank match, so a visible message is often easier to troubleshoot. If the formula itself displays as text, check that the cell is not formatted as Text and that the formula begins with an equals sign.

Alternatives when you need more than one result

XLOOKUP is designed to return a corresponding result for a match, not to spill every row that satisfies several conditions. If you need a list of all matching rows in Microsoft 365 or Excel 2021 and later, FILTER is often a better fit. With product names in A2:A100, regions in B2:B100, return values in C2:C100, and criteria in H2 and H3, use:

=FILTER(C2:C100,(A2:A100=H2)*(B2:B100=H3),"No matches")

That returns all matching values and spills them into cells below the formula. Make sure the spill area is empty. FILTER is available in Microsoft 365 and Excel 2021 or later, as is XLOOKUP. In older Excel versions, a helper column or an INDEX/MATCH-based formula may be needed, and the exact solution depends on whether you want one result or a full list.

For a lookup across several columns where the combination is unique, another possibility is concatenating a key, but the delimiter and data-quality caveats still apply. Avoid assuming that XLOOKUP can return multiple matches simply because the lookup array contains several TRUE values: it selects one match according to its search direction. Use FILTER when the task is to retrieve all matches.

Frequently Asked Questions

Can XLOOKUP use two criteria?
Yes. Multiply the two TRUE/FALSE comparisons in the lookup array, then search for 1. With matching rows in both tests, the product is 1; otherwise, it is 0. This is the standard pattern for two conditions that must both be met.

Does XLOOKUP with multiple criteria return the first or last match?
It returns the first match by default. Set the search mode argument to -1 to search from the bottom and return the last match. “First” and “last” refer to the order of the rows, so arrange or sort the source data appropriately for your purpose.

Can I use XLOOKUP with multiple criteria in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019. For an older edition, consider INDEX and MATCH or a helper column, taking care to choose a method supported by that version.

Why does my multiple-criteria XLOOKUP return #N/A?
Usually no row satisfies every condition exactly. Check spelling, spaces, number-versus-text mismatches, and whether each criteria range covers the same rows. You can also use XLOOKUP’s not-found argument to display a helpful message, but that message does not fix a mismatch.

Can XLOOKUP return all matching rows?
No. XLOOKUP returns one corresponding result: the first match by default or the last when reverse search is specified. Use FILTER in Microsoft 365 or Excel 2021 and later when you need all matching results to spill into the worksheet.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.