XLOOKUP Multiple Criteria: Formula and Examples

Formulas & functions · Updated November 18, 2026

Yes. In Excel for Microsoft 365 or Excel 2021 and later, XLOOKUP can match multiple criteria by multiplying the tests for an AND condition, then looking for 1. With customer names in A2:A100, regions in B2:B100, amounts 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").

How the multiple-criteria formula works

XLOOKUP normally searches one lookup array for one lookup value. It does not have separate arguments for a first criterion, a second criterion, and so on. Instead, the formula compares each row with every requested condition and combines the results into an array that XLOOKUP can search.

In the example formula, (A2:A100=H2) creates a series of TRUE or FALSE results for the customer test. The second comparison does the same for the region. Multiplying the two arrays treats TRUE as 1 and FALSE as 0: a row that meets both tests produces 1, while a row that fails either test produces 0. XLOOKUP searches for 1 and returns the value from the corresponding row of D2:D100.

For example, suppose row 2 contains M. Chen, North, INV-104, and 850, while row 3 contains M. Chen, South, INV-118, and 1,250. If H2 contains M. Chen and H3 contains South, the formula returns 1,250. The row must satisfy both conditions; a match on the customer alone is not enough.

The final argument, "Not found", is optional. It supplies a readable result when no row matches instead of displaying the usual not-found error. If you omit it, XLOOKUP returns #N/A when no match is found.

Set up the data and enter the formula

Keep each criterion in its own column and make sure the lookup and return ranges cover the same rows. Put the values to search for in separate input cells so you can change them without editing the formula. For the example above:

  • A2:A100 holds customer names.
  • B2:B100 holds regions.
  • D2:D100 holds the amounts to return.
  • H2 holds the requested customer, and H3 holds the requested region.

Enter the formula in the cell where you want the answer. In Microsoft 365 for Windows, click that cell, type or paste the formula in the formula bar or directly in the cell, and press Enter. Excel for Mac and Excel for the web use the same formula. The ribbon and keyboard behavior can differ slightly by platform, but no special menu command is needed.

When the data grows, Excel Tables can make formulas easier to read and maintain. If a table named Orders has Customer, Region, and Amount columns, and H2 and H3 contain the requested customer and region, use =XLOOKUP(1,(Orders[Customer]=H2)*(Orders[Region]=H3),Orders[Amount],"Not found"). Table references generally expand as you add rows, unlike fixed ranges such as A2:A100, which may need extending if new records go below row 100.

Adding a third or more criteria

For an AND match across three fields, multiply all three comparisons. With customer names in A2:A100, regions in B2:B100, years in C2:C100, amounts in D2:D100, and the requested values in H2, H3, and H4, use =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3)*(C2:C100=H4),D2:D100,"Not found").

Each additional comparison must cover the same rows. You can extend the pattern with more terms, such as product, department, and status, but keep the formula understandable. If the conditions are numerous or change often, consider adding a helper column or using a more structured data model rather than building an exceptionally long formula.

Comparisons normally use Excel’s standard equality behavior, which is not case-sensitive. Thus, a criterion for north can match North. If case matters, use EXACT for the text condition. With names in A2:A100, regions in B2:B100, amounts in D2:D100, the requested name in H2, and region in H3, use =XLOOKUP(1,EXACT(A2:A100,H2)*(B2:B100=H3),D2:D100,"Not found"). EXACT distinguishes uppercase and lowercase letters. It is usually unnecessary for ordinary business lookups and can make the formula less familiar to other spreadsheet users.

AND criteria versus OR criteria

Multiplication is the usual pattern when every condition must be true: customer is M. Chen AND region is South. If you instead want a row that satisfies either condition, use an OR test. With names in A2:A100, regions in B2:B100, return values in D2:D100, and the requested name and region in H2 and H3, use =XLOOKUP(TRUE,((A2:A100=H2)+(B2:B100=H3))>0,D2:D100,"Not found").

Addition marks a row with at least one true condition, and the comparison to zero turns the result into TRUE or FALSE. This returns the first row that matches the name or the region; it does not return every qualifying row. A row that meets both conditions still counts as a match, not as two separate results. Be specific about whether your requirement is AND or OR before choosing the formula, because the two patterns answer different questions.

Returning the first match, last match, or several fields

By default, XLOOKUP searches from the first item to the last and returns the first matching row. If several records share the same criteria, that default may be exactly right—for example, when the data is sorted by date and the earliest listed match is wanted. It does not automatically sum, average, or combine all matching records.

To search from the bottom upward and return the last matching record, set the match mode to 0 for exact match and the search mode to -1. With criteria in A2:A100 and B2:B100, a return value in D2:D100, and search values in H2 and H3, use =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),D2:D100,"Not found",0,-1). This is useful when later rows represent newer entries, provided the row order really reflects that sequence.

XLOOKUP can also return more than one column from the matched row. With customer and region criteria in A2:A100 and B2:B100, three fields to return in C2:E100, and requested values in H2 and H3, use =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:E100,"Not found"). In Microsoft 365 or Excel 2021 and later, the result can spill into neighboring cells. Keep those cells empty; otherwise Excel may show a #SPILL! error. The returned columns stay together, making this a convenient way to retrieve an order number, date, and amount from one matching record.

Common errors and practical checks

If the formula returns Not found unexpectedly, check the data before changing the formula. A value that looks identical may contain a trailing space, a nonprinting character, or a different underlying type. For instance, the number 123 and text stored as 123 are not always equivalent in a comparison. Check both the criteria cells and the source columns, and normalize inconsistent data where appropriate.

  • Check range dimensions: Each criteria range must cover the same number of rows as the other criteria ranges, and the return range must align to those same records. A misaligned range can return a result from the wrong row.
  • Check the intended result: If multiple rows meet the conditions, XLOOKUP returns one row, normally the first. Use the bottom-up search option for the last match, or use another approach if you need every match.
  • Check the not-found setting: A custom message can make a missing match easier to read, but it can also hide the distinction between a genuinely absent record and a data-quality problem. During troubleshooting, temporarily omit the custom message to see the underlying error.
  • Check for blocked spill cells: A multi-column return needs empty cells to its right. Clear the obstructing cells if Excel reports #SPILL!.
  • Check the Excel version: XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported Mac and web editions. Excel 2019 and earlier do not include the function; a formula using it may show #NAME? there.

In English-language Excel, the formulas here use commas between arguments. Some regional settings use semicolons as the list separator, so Excel may require semicolons when you enter a formula. That is a locale setting difference, not a different XLOOKUP method.

XLOOKUP compared with alternatives

For modern Excel, XLOOKUP is often easier to read than the older INDEX and MATCH combination, particularly because it includes a not-found argument and can return values from columns on either side of the lookup data. Its multiple-criteria logic still needs to be built into the lookup array, as shown above. XLOOKUP is not a separate “multiple criteria” function.

For older Excel versions without XLOOKUP, INDEX and MATCH can implement a similar lookup, but array-formula requirements vary by version and the formula is less straightforward. If a workbook must be opened by people using Excel 2019 or earlier, test compatibility and choose a formula supported by their versions. Avoid assuming that a formula working in Microsoft 365 will work in every spreadsheet program.

If the goal is to display all rows that meet conditions rather than retrieve one matching value, FILTER may fit better in Microsoft 365 or Excel 2021 and later. With names in A2:A100, regions in B2:B100, and full records in A2:D100, use =FILTER(A2:D100,(A2:A100=H2)*(B2:B100=H3),"No matches"). This returns all records meeting both tests and spills them into adjacent cells. Unlike XLOOKUP, it is designed to return a set of matching rows, not just the first or last one.

Frequently Asked Questions

Can XLOOKUP use multiple criteria without a helper column?
Yes. Multiplying the comparison arrays inside the lookup-array argument creates an AND match without changing the source data. A helper column is optional and can be useful when the combined key needs to be reused or audited.

Does XLOOKUP multiple criteria return every match?
No. XLOOKUP returns one match: the first by default, or the last if you choose a bottom-up search. Use FILTER when you need all matching records and your Excel version supports it.

Can I use XLOOKUP with three criteria?
Yes. Add a third comparison to the product, such as (A2:A100=H2)*(B2:B100=H3)*(C2:C100=H4), and make sure every range covers the same rows.

Why does my formula show #N/A or Not found?
No row may satisfy every condition, or the visible values may differ because of spaces, text-versus-number types, or inconsistent data. Confirm that the criteria cells contain the expected values and that the source ranges line up.

Is XLOOKUP available in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or earlier. For a workbook shared with older Excel versions, use a compatible alternative or confirm that recipients can open it in a supported version.

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.