To use XLOOKUP with multiple criteria in Excel, multiply the criteria tests and look for 1: =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.
Basic multiple-criteria formula
In Microsoft Excel for Microsoft 365 on Windows, XLOOKUP normally compares one lookup value with one lookup array. To match more than one condition, give it an array of combined TRUE/FALSE tests instead. Multiplying the tests makes a row qualify only when every condition is true, which is the usual way to express AND logic.
For example, imagine a sales table with region in A2:A100, product in B2:B100, and sales amount in C2:C100. Cell H2 contains the region to find and H3 contains the product. The formula in the opening example returns the sales amount for the first row that has both requested values. If the matching row has 1,250 in column C, the result is 1,250. If no row satisfies both conditions, the formula displays Not found.
The lookup value is 1 because multiplying TRUE values produces 1, while a FALSE condition makes the product 0. XLOOKUP searches that calculated array for 1. The final argument, “Not found”, is optional; it supplies a readable result when no match exists rather than displaying the default #N/A error.
How the criteria calculation works
Each comparison creates an array of TRUE and FALSE values, one for each table row. In the example, A2:A100=H2 checks each region, while B2:B100=H3 checks each product. Multiplying corresponding results combines the tests row by row. A row where both comparisons are TRUE becomes 1; a row where either comparison is FALSE becomes 0.
This method works with text, numbers, dates, and cell references, provided the values being compared are compatible. A date criterion, for example, should generally refer to a real Excel date value rather than text that only looks like a date. The criteria ranges and return range must also cover the same rows. Mixing A2:A100 with B2:B99 can produce a #VALUE! error because the arrays have different sizes.
Exact matching is the default for XLOOKUP, so you do not need to add a match-mode argument for ordinary equality tests. The comparisons themselves are also not case-sensitive: “East” and “EAST” are treated as equal. If the business rule requires distinguishing capitalization, use a case-sensitive approach such as a helper column with EXACT, rather than assuming XLOOKUP makes text comparisons case-sensitive.
Worked example with department and month
Suppose a worksheet lists department in A2:A50, month in B2:B50, and budget in C2:C50. H2 contains “Support” and H3 contains a date representing the first day of the month being searched for. The month column must contain comparable date values; formatting dates to display as month names does not change the underlying values.
With departments in A2:A50, month dates in B2:B50, budgets in C2:C50, and the requested department and date in H2 and H3, enter:
=XLOOKUP(1,(A2:A50=H2)*(B2:B50=H3),C2:C50,"No matching budget")
If the first Support row for the selected month has a budget of 18,400, the formula returns 18,400. If the month is stored as text in the table but as a real date in H3, the comparison may fail even though both cells display the same month. Resolve that mismatch by making both sides real dates or both sides consistently formatted text; do not rely on how the cells look on screen.
You can add a third requirement by multiplying another test. For instance, a table could include department, month, and status, and the lookup could require all three to match. Keep the ranges aligned, and use a separate cell for each chosen criterion when the formula will be reused or reviewed by other people.
Combining AND and OR conditions
Multiplication means AND: every multiplied test must be true. Addition can express OR because a row meeting either test produces a nonzero result. For a lookup requiring either East or West in column A, while also requiring the product in H3 in column B, the formula can combine addition and multiplication.
With region values in A2:A100, product values in B2:B100, results in C2:C100, the two allowed regions in H2 and H3, and the required product in H4, use:
=XLOOKUP(1,((A2:A100=H2)+(A2:A100=H3))*(B2:B100=H4),C2:C100,"Not found")
The parenthesized addition makes either region acceptable; the multiplication then requires the product test as well. A matching row will have a final value of 1. Avoid leaving the grouping unclear: parentheses make the intended logic easier to inspect and reduce the risk of changing the meaning when editing the formula. For a long set of OR choices, a helper column or a more explicit formula may be easier to maintain.
Return several fields from the matching row
XLOOKUP can return more than one column when its return array spans multiple columns. This is useful when a match needs to show a name, category, and price together instead of making separate lookup formulas. In Microsoft 365, the results can spill into neighboring empty cells.
With criteria in A2:A100 and B2:B100, fields to return in C2:E100, and the requested criteria in H2 and H3, use:
=XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:E100,"Not found")
If the matching row contains three fields, the formula returns all three across the worksheet. Make sure the cells to the right are clear so the result can spill. If something already occupies a required spill cell, Excel displays a #SPILL! error; clear the obstructing cells or move the formula. The function returns fields from one matching row, not every row that meets the conditions. Use FILTER when you need all qualifying records rather than just the first match.
Duplicates and choosing the first or last match
By default, XLOOKUP searches from the first item to the last and returns the first match. If multiple rows share the same combination of criteria, the formula does not automatically total them or report that there are duplicates. Confirm that the criteria identify a unique row if selecting the first match could give the wrong result.
To return the last matching row instead, use XLOOKUP’s search-mode argument. The argument order is lookup value, lookup array, return array, if-not-found, match mode, and search mode. A search mode of -1 searches from last to first; 0 specifies exact matching, which is already the default but is included below for clarity.
With criteria in A2:A100 and B2:B100, return values in C2:C100, and the requested values in H2 and H3, use:
=XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:C100,"Not found",0,-1)
This returns the value from the bottom-most matching row in the stated ranges. It is not the same as finding the largest or most recent value; it simply searches in reverse worksheet order. If “latest” means the greatest date, make sure the data is sorted appropriately or use a method that explicitly compares the dates.
Common errors and how to fix them
- #N/A: No row met all criteria, or the optional not-found result was omitted. Check spelling, extra spaces, date values, and whether numbers are stored as text.
- #VALUE!: The criteria arrays or return array may have different heights. Make all ranges start and end on corresponding rows.
- #SPILL!: A multi-column result cannot occupy one or more cells beside the formula. Clear the blocking cells.
- #NAME?: The Excel version may not recognize XLOOKUP, or the function name may be misspelled. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or earlier perpetual versions.
- Unexpected first result: Several rows may match. Decide whether the first match is appropriate, search from the bottom, or use FILTER to return multiple rows.
Also check for hidden spaces in imported text. TRIM can remove ordinary extra spaces, but it does not remove every nonbreaking or unusual character that can arrive from external systems. Comparing cell contents directly or using a helper column to clean imported fields can reveal the cause. Avoid applying whole-column array comparisons to very large datasets when a realistic bounded range or Excel Table column will do; smaller ranges are easier to audit and can reduce unnecessary calculation.
Helper columns and other approaches
Multiplying criteria arrays is compact and avoids changing the source table. A helper key is another option when the same combination is looked up repeatedly or the logic is easier to explain as one combined identifier. For example, a helper column can join region and product, and a lookup value can join the requested region and product in the same order. Both sides must use the same construction.
With region in A2:A100 and product in B2:B100, enter this formula in D2 and fill it down to create a key:
=A2&"|"&B2
With the resulting keys in D2:D100, return values in C2:C100, and requested region and product in H2 and H3, use:
=XLOOKUP(H2&"|"&H3,D2:D100,C2:C100,"Not found")
The vertical bar is a separator, but this technique is safest when that character cannot appear in either field. Otherwise two different pairs can potentially create the same combined text key. Adding a helper key also changes the worksheet structure and requires the key formula to be filled or maintained for new rows. Excel Tables can help keep formulas and ranges aligned as data grows.
For returning every matching record, FILTER is usually a better fit than XLOOKUP. For a single result with criteria that can identify the desired row, XLOOKUP is generally simpler. A helper column is useful for repeated lookups or transparency, while the multiplied-condition method is convenient for a one-off formula and keeps the source data unchanged.
Version support and practical setup
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported Excel versions for Windows, Mac, and the web. The formula logic described here is the same across those platforms, although interface details can differ. If a workbook must be used in Excel 2019 or an earlier version, XLOOKUP may show as an unrecognized function; use a compatible alternative such as INDEX with MATCH or upgrade the workbook’s target version.
When possible, convert the data range to an Excel Table using Insert > Table. Tables expand as new rows are added and make structured references possible, which can make criteria formulas easier to read. For example, meaningful column names are clearer than unexplained cell references, though structured-reference syntax can take a little practice. Regardless of range style, verify that your criteria columns and return fields refer to the same records.
Before relying on a lookup in a report, test it with a known matching row, a combination that should not match, and a duplicate combination if duplicates are possible. These checks help distinguish a formula issue from a data-quality issue, such as inconsistent labels or mixed date formats.
Frequently Asked Questions
Can XLOOKUP use three or more criteria?
Yes. Add another comparison and multiply it into the lookup array for another AND condition. Keep all criteria ranges the same size as the return range.
Does XLOOKUP with multiple criteria return every match?
No. XLOOKUP returns one matching result, normally from the first matching row. Use FILTER if you need all rows that meet the conditions.
How do I make multiple criteria an OR condition?
Add the tests for the alternatives inside parentheses, then use multiplication for any separate conditions that must also be true. Check the formula’s parentheses carefully so the OR group is evaluated together.
Why does my multiple-criteria XLOOKUP return #N/A?
At least one comparison may not match exactly. Check for trailing spaces, text-versus-number differences, inconsistent dates, and criteria entered in the wrong cells. You can include an if-not-found message to make a missing match easier to interpret.
Can XLOOKUP distinguish uppercase and lowercase text?
Not with ordinary equality comparisons; those comparisons are case-insensitive. If capitalization is part of the matching rule, use a case-sensitive method such as EXACT in a suitable lookup calculation or helper column.
Related
- Excel Visible Cells Only Shortcut: How It Works in Excel
- Excel Uppercase Shortcut: Fast Ways to Change Text
- Excel Zoom Shortcut: How to Zoom In, Out, and Set Scale
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Excel Grading: Match Scores to Letter GradesTo assign a letter grade from a numeric score in Excel, put the grade cutoffs in ascending order…
- XLOOKUP Wildcards: Examples, Rules, and Common FixesWith region names in A2:A6 and matching IDs in B2:B6, use =XLOOKUP("North*",A2:A6,B2:B6,"Not found",2) to return 101, the ID…
- Excel Date Minus Days: Formulas and ExamplesTo subtract calendar days from a date in Excel, subtract a number from the date cell: if the…
- COUNTIF in Excel: Count Cells That Match a Specific ValueTo count cells in Excel that match a value, use =COUNTIF(A2:A20,"Apple") when the values are in A2:A20 and…
- XLOOKUP Example: Step-by-Step Formula Guide for ExcelTo find a product’s price by its ID in Excel, enter =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found") in the result cell. If…
- COUNTIF Not Including Zero? Excel Formula ExamplesIn Excel for Microsoft 365, use =COUNTIF(A2:A20,"<>0") to count cells whose values are not zero, assuming your data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.