Excel XLOOKUP Multiple Criteria: Formulas and Examples

Formulas & functions · Updated November 20, 2026

Yes. In Microsoft Excel for Microsoft 365, XLOOKUP can match multiple criteria by multiplying comparison tests so that a row qualifies only when every condition is true. For example, with customer names in A2:A100, regions in B2:B100, sales in D2:D100, and the requested customer and region in H2 and H3, use =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),D2:D100,"Not found"). It returns the sales value for the first row meeting both conditions, or Not found if there is no match.

What multiple criteria means

A standard XLOOKUP searches one lookup array for a matching value. Multiple-criteria lookup adds conditions from other columns, such as finding a particular customer in a particular region, or a product for a specified month. The key question is whether all conditions must be true or whether any one of them is enough.

When all conditions must be true, the logic is AND. In the opening example, a row is eligible only if the customer in column A matches H2 and the region in column B matches H3. A match on customer alone, or region alone, is not enough. This is the most common multiple-criteria use of XLOOKUP.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not included in Excel 2019 or Excel 2016. On Windows, enter the formula in a cell and press Enter; in Microsoft 365, the comparison arrays are evaluated without the old Ctrl+Shift+Enter array-formula keystroke. The same formula approach is available in current Excel for Mac and Excel for the web, although ribbon placement and some editing details can differ.

How the AND formula works

Each comparison creates an array of TRUE and FALSE results, one for each row. For example, the customer test checks A2:A100 against H2, while the region test checks B2:B100 against H3. Multiplication coerces TRUE to 1 and FALSE to 0. A row gets 1 only when both tests are true; any false condition makes the product 0. XLOOKUP then searches for 1.

In the opening formula, the lookup value is 1, the calculated lookup array is the two tests multiplied together, and D2:D100 is the return array. The fourth argument supplies a friendly result when no row qualifies. Because the ranges cover rows 2 through 100, the formula returns the sales value from the first qualifying row in that range. It does not add sales from every matching row.

For a three-condition example, suppose a worksheet lists customer in A, region in B, month in C, and sales in D. The requested customer, region, and month are in H2, H3, and H4. With those layouts, use =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3)*(C2:C100=H4),D2:D100,"Not found"). Each additional AND condition contributes another comparison array to the product. Keep the lookup and return ranges the same height so every test and returned value refers to the same row.

Use cell references and data carefully

Putting search criteria in cells makes a formula easier to reuse. You can change H2 or H3 without rewriting the formula, and you can copy the formula to other locations. If you plan to copy it down or across, consider whether the criteria cells should stay fixed. For example, dollar signs can lock a reference such as $H$2 when copying; relative and absolute references follow the usual Excel rules.

Check that compared values have compatible types. A number stored as text may not compare like a true number, and dates can be affected when one value is text rather than an Excel date serial value. Leading or trailing spaces in imported text can also make two names that look alike fail to match. Cleaning the source data or correcting the cell type is usually better than adding complicated conversion logic to the lookup formula.

XLOOKUP’s ordinary exact matching is not case-sensitive: for example, it normally treats north and North as equivalent text. If capitalization is meaningful in your data, the basic equality comparisons do not enforce case-sensitive matching. Also be aware that a blank criteria cell can match blank entries in the corresponding source column. Decide whether that is intended before relying on an empty input as a meaningful criterion.

Use OR conditions when any match is acceptable

Not every search is an AND search. If you need a customer in one region and a status that is either Approved or Pending, the region condition still has to be true, but either status may qualify. Addition can represent OR because a true comparison contributes 1. Wrapping the sum in a greater-than-zero test turns one or more successful status checks into TRUE.

Assume customer is in A2:A100, status in B2:B100, and the value to return in C2:C100. Put the requested customer in H2 and the two acceptable statuses in H3 and H4. Use =XLOOKUP(1,(A2:A100=H2)*(((B2:B100=H3)+(B2:B100=H4))>0),C2:C100,"Not found"). This returns the first row for that customer whose status matches either input. If the entire search should match one of several customers or one of several regions, build the OR group around the relevant comparison and keep the AND groups separate.

Be precise about the grouping parentheses. Multiplication between condition groups means AND, while addition inside a group can mean OR. Without grouping, a formula can apply the intended logic in a different order or allow a row through when it should not qualify. Testing the formula against a few known records is a sensible way to confirm the logic.

Return more than one column

XLOOKUP can return a multi-column range, not just one column. This is useful when the match identifies a record and you want to display several related fields, such as a product name and its price. The return range must still line up row by row with the lookup arrays.

Suppose A2:A100 contains product IDs, B2:B100 contains regions, and C2:D100 contain product name and price. The requested ID is in H2 and region is in H3. With this layout, enter =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:D100,"Not found"). In Microsoft 365, the two returned fields spill into adjacent cells. Leave those cells empty; if a value or another obstruction is in the spill area, Excel displays a #SPILL! error. In older versions without dynamic arrays, returning multiple columns this way may not work as expected.

Return the last qualifying match

By default, XLOOKUP searches from the first item to the last. If duplicate records exist and the newest or last-listed qualifying entry is the one you need, change the search mode to search in reverse. This does not combine duplicate results; it selects one qualifying row according to the search direction.

For customer names in A2:A100, regions in B2:B100, and sales in D2:D100, with the criteria in H2 and H3, use =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),D2:D100,"Not found",0,-1). The fifth argument, 0, requests exact matching; the sixth argument, -1, searches from last to first. This is helpful when records are ordered chronologically and the latest record is at the bottom, but it depends on that ordering being reliable.

Find all matching rows with FILTER

XLOOKUP is designed to return a result for one match, even when several records satisfy the criteria. If you want every matching row, FILTER is usually a better fit in Microsoft 365 or Excel 2021 and later. It returns a spilled array containing all rows whose inclusion test is true.

Assume customer is in A2:A100, region in B2:B100, and the records to return are in A2:D100. Put the requested customer and region in H2 and H3. Use =FILTER(A2:D100,(A2:A100=H2)*(B2:B100=H3),"No matches"). If three records meet both conditions, the formula returns all three rows rather than choosing the first. As with other dynamic-array results, the cells below and to the right must be clear for the results to spill.

Choose based on the question: use XLOOKUP when the answer should be one value or one record, and FILTER when the answer should include every qualifying record. If you need a total rather than a returned record, a function such as SUMIFS may suit the task better; it sums values meeting criteria instead of locating a row.

Common problems and fixes

  • The formula says Not found: Check every criterion, including spaces, spelling, date values, and whether numbers are stored as text. Temporarily test each condition separately to identify which one fails.
  • You get an unexpected record: XLOOKUP returns the first qualifying row by default. Check for duplicates and choose reverse search only if the last qualifying row is genuinely the desired one.
  • You see #VALUE!: Confirm that each comparison range and the return range have compatible dimensions. For example, do not compare 99 rows but provide a return range with 100 rows.
  • You see #NAME?: The Excel version may not support XLOOKUP, or the function name may have been mistyped. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, not Excel 2019 or 2016.
  • You see #SPILL!: Clear cells that block a multi-column XLOOKUP result or a FILTER result. A single-cell return does not need a spill area.
  • The result is correct for one row but not copied formulas: Check whether criteria-cell references should be absolute. Also check that range endpoints remain consistent when copying.

For large worksheets, use reasonably sized ranges or Excel Tables rather than referencing entire columns in several array calculations. Tables expand as records are added and make formulas easier to read with structured references, though the exact formula text differs from the cell-range examples here. Avoid sorting or editing source data in a way that separates fields belonging to the same record.

XLOOKUP versus concatenating criteria

Another approach joins the criteria into a single lookup value and joins the corresponding source columns in the same way. This can look straightforward, but the separator and source data must be handled carefully. If the chosen separator occurs inside a customer name or region, two different combinations can produce the same joined text. Boolean multiplication avoids that particular delimiter-collision risk and makes each condition visible.

For a comparison, assume customer names are in A2:A100, regions in B2:B100, sales in D2:D100, and the criteria are in H2 and H3. A concatenation version is =XLOOKUP(H2&"|"&H3,A2:A100&"|"&B2:B100,D2:D100,"Not found"). This is valid in current Microsoft 365 Excel, but it still returns only the first match and can be less transparent to maintain. Use the multiplication method as the default unless joining the fields has a specific advantage in your workbook.

Frequently Asked Questions

Can XLOOKUP use three or more criteria? Yes. Multiply each additional equality test into the lookup array when all conditions must be true. Ensure every source range has the same number of rows and that the criteria cell for each test contains the intended value.

Does XLOOKUP with multiple criteria return the first match? Yes. Its default search direction is first to last, so it returns the first qualifying result. Use the reverse search mode when the last qualifying record is the one you want, or use FILTER when you need all matches.

Can I use multiple criteria from the same column? Yes. For an either/or search, test the column against each acceptable value and group those comparisons as an OR condition. For example, the status example above accepts either of two statuses while still requiring the customer to match.

Why does XLOOKUP return the wrong duplicate? The formula may be finding an earlier row that also meets the criteria. Check the source for duplicates and decide whether the first record, last record, or every record is appropriate. XLOOKUP does not automatically know which duplicate is most current.

Can I use XLOOKUP with multiple criteria in Excel 2019? No. Excel 2019 does not include XLOOKUP. Use a compatible lookup method available in that version or open the workbook in Microsoft 365 or Excel 2021 or later. A formula using XLOOKUP may show an unsupported-function error in an older installation.

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.