To use XLOOKUP for a conditional search in Excel, put the condition in its lookup array; for multiple conditions that must all be true, multiply the tests. For example, with products in A2:A100, regions in B2:B100, sales in C2:C100, the requested product in E2, and region in F2, use =XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"Not found"). The formula returns the sales value from the first row where both conditions match.
What a conditional XLOOKUP does
XLOOKUP normally searches one lookup array for one lookup value and returns the corresponding item from a return array. A conditional search changes the lookup array into a calculated series of TRUE and FALSE results—or 1 and 0 values—based on one or more tests. XLOOKUP then finds the result that represents a match and returns the value from the corresponding position.
This is useful when a value is not unique by itself. An order number might recur in several regions, for example, or an employee name might appear in more than one department. Adding a second condition lets Excel distinguish the intended row. The lookup and return arrays must cover corresponding rows and should be the same size; otherwise, the result may be wrong or Excel may return an error.
A conditional XLOOKUP still returns one result, not every row that meets the conditions. By default, it returns the first matching result from top to bottom. If several rows qualify, decide whether the first one is meaningful, whether you want the last one, or whether a function that returns multiple rows is a better fit.
How the formula works
In the opening example, each comparison creates an array of TRUE and FALSE values. Multiplying the two arrays applies AND logic: a row produces 1 only when both tests are TRUE, while a row that fails either test produces 0. XLOOKUP searches for 1 and returns the value at the same position in the sales range.
For instance, if row 4 contains the requested product and region and its sales value is 125, that row creates the matching 1 and the formula returns 125. If no row meets both tests, the optional fourth argument displays Not found instead of the standard not-found error. Text values in that argument need quotation marks in the Excel formula.
Multiplying conditions is a common pattern, not a special XLOOKUP setting. It works because Excel can calculate comparisons across ranges as arrays. Keep the ranges aligned: comparing A2:A100 with B3:B101 would test mismatched rows even though the ranges have the same number of cells.
Searching with one condition
For a single condition, you can often use a normal XLOOKUP without constructing a more complicated array. Suppose an inventory sheet has item codes in A2:A50 and quantities in B2:B50, with the item code to find in E2. The formula below returns the corresponding quantity.
With item codes in A2:A50, quantities in B2:B50, and the target code in E2, enter:
=XLOOKUP(E2,A2:A50,B2:B50,"Item not found")
If E2 contains ITM-204 and that code is on row 9, the result is the quantity from B9. XLOOKUP uses exact matching by default, so this is generally suitable for identifiers. A lookup value must still be represented consistently: extra spaces, different punctuation, or numbers stored as text can prevent an apparent match.
Use a condition array when the condition itself needs to be calculated—for example, when you are matching against a rule rather than a single item code—or when you need to combine tests. For ordinary one-value matching, the simpler form is easier to read and maintain.
Combining conditions with AND logic
Multiplication is the most direct way to require every condition to be true. Imagine a price list with product names in A2:A20, sizes in B2:B20, and prices in C2:C20. The requested product is in E2 and requested size in F2. The formula returns the price only when the product and size on the same row match both inputs.
With product names in A2:A20, sizes in B2:B20, prices in C2:C20, product in E2, and size in F2, enter:
=XLOOKUP(1,(A2:A20=E2)*(B2:B20=F2),C2:C20,"No matching product and size")
If E2 is Mug and F2 is Large, and the first row with both values has a price of 18.5, the formula returns 18.5. A row with Mug and Small does not qualify; neither does a row with Plate and Large. Add another condition by multiplying its comparison into the lookup array, keeping all ranges the same height.
To make the result easier to audit, put the requested criteria in separate cells and label them. That lets someone change the product or size without editing the formula. It also makes a missing result easier to investigate: check each condition independently, then confirm that the required combination actually occurs in one row.
Using OR logic for alternatives
If either of two conditions can qualify, use addition and test whether the result is greater than zero. This creates a TRUE/FALSE array that XLOOKUP can search for TRUE. The greater-than-zero test matters: if a row satisfies both alternatives, adding the two TRUE values produces 2, not 1.
With categories in A2:A30, product names in B2:B30, return values in C2:C30, and either acceptable category in E2 or F2, enter:
=XLOOKUP(TRUE,((A2:A30=E2)+(A2:A30=F2))>0,C2:C30,"No match")
If E2 contains Hardware and F2 contains Garden, this returns the value from the first row whose category is either one. Because XLOOKUP returns the first qualifying row by default, row order determines which result appears when both categories have matches. Use separate formulas or a filtering function if you need to see all qualifying records rather than the first.
Wildcard, case-sensitive, and partial searches
XLOOKUP supports wildcard matching when its match-mode argument is set to 2. In that mode, an asterisk represents any sequence of characters and a question mark represents one character; a tilde can be used to treat a wildcard character literally. This is handy when a user knows only part of a code or name. Wildcard matching is not the default, so include the match-mode argument deliberately.
With names in A2:A40, department values in B2:B40, and a search fragment in E2, enter:
=XLOOKUP(E2,A2:A40,B2:B40,"No match",2)
If E2 contains Ana*, Excel can match a name beginning with Ana and return the corresponding department. Wildcards are not a substitute for a second condition: if you need both a partial name and a department restriction, build a combined condition array instead.
Standard text comparisons in Excel are generally case-insensitive. To distinguish, for example, ABC from Abc, use EXACT to create a case-sensitive TRUE/FALSE array. With codes in A2:A30, return values in B2:B30, and the case-sensitive target in E2, enter:
=XLOOKUP(TRUE,EXACT(A2:A30,E2),B2:B30,"No exact-case match")
This returns the value from the first cell whose text matches E2 with the same letter case. EXACT does not ignore extra spaces, so clean the source data if spaces are accidental. For large or complicated workbooks, a helper column that clearly identifies the desired condition can make the logic easier to review.
Returning the last matching row
Sometimes the most recent or final entry is the useful one. XLOOKUP can search in reverse by setting its search-mode argument to -1. This changes the direction of the search; it does not sort the data or determine which date is latest. The last row is only the latest record if the rows are ordered that way.
With transaction IDs in A2:A100, dates in B2:B100, amounts in C2:C100, and the requested ID in E2, enter:
=XLOOKUP(E2,A2:A100,C2:C100,"ID not found",0,-1)
This exact-match formula searches from the bottom upward and returns the amount from the last row containing the ID. The 0 specifies exact matching, and -1 specifies reverse search. If the transaction data is not arranged chronologically, this returns the last occurrence in worksheet order, not necessarily the greatest date. For a date-based requirement, confirm the sort order or use a formula designed to compare dates.
Common errors and ways to troubleshoot
- No match appears: Check for leading or trailing spaces, inconsistent number-versus-text values, spelling differences, and criteria entered in the wrong cells. Try testing each condition separately.
- The wrong row is returned: XLOOKUP returns the first match unless you specify reverse search. Look for duplicate combinations and consider whether the first or last match is appropriate.
- The formula returns an error: Confirm that lookup and return arrays are aligned and have compatible dimensions. For a multi-condition formula, every condition range should cover the same rows.
- The formula is not recognized: XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not available in Excel 2019 or Excel 2016. Excel for the web also supports XLOOKUP.
- Copied formulas behave unexpectedly: Use absolute references such as $A$2:$A$100 when you want ranges to remain fixed as a formula is copied. Relative references can be useful when the ranges should move.
Excel for Microsoft 365 on Windows uses commas between formula arguments in the examples here. Regional settings can require semicolons instead. Excel for Mac supports XLOOKUP in Microsoft 365 and compatible newer versions; the formula logic is the same, though separators can also depend on regional settings. The function is not available in older perpetual Excel versions simply because a workbook is opened on a newer computer.
When FILTER is a better choice
Use XLOOKUP when you want one corresponding result. If the requirement is to display every row satisfying a condition, FILTER is usually more suitable in modern Excel. It returns a dynamic array that can spill into nearby cells, so make sure the destination cells are empty.
With product names in A2:A100, regions in B2:B100, sales in C2:C100, and product and region criteria in E2 and F2, enter:
=FILTER(C2:C100,(A2:A100=E2)*(B2:B100=F2),"No matches")
This returns all sales values whose rows meet both criteria, rather than only the first. To return entire matching rows, use a data range covering the columns you want displayed as FILTER’s first argument. FILTER is available in Microsoft 365 and Excel 2021 or later. In older Excel versions, alternatives include helper columns, Advanced Filter, or a carefully constructed INDEX and MATCH formula, though those approaches differ in setup and flexibility.
Frequently Asked Questions
Can XLOOKUP search using two criteria?
Yes. Multiply the two comparison arrays to require both criteria, then search for 1. Keep each comparison range aligned with the others and with the return range.
Does XLOOKUP return the first or last match?
By default, it returns the first match from the start of the lookup array. Set the search-mode argument to -1 to search from the last row upward and return the last match in worksheet order.
Can XLOOKUP return all matching rows?
No. XLOOKUP returns one match. Use FILTER in Microsoft 365 or Excel 2021 and later when you want all rows that satisfy one or more conditions.
Can XLOOKUP do a partial match?
Yes. Set match mode to 2 and use wildcard characters such as * or ? in the lookup value. A wildcard can represent part of a text value, but the formula must explicitly enable wildcard matching.
Why does XLOOKUP say it cannot find a value that is visible?
The displayed values may differ underneath because of spaces, data types, or characters that look alike. Check the source and criteria cells, and verify that the comparison ranges include the row you expect.
Is XLOOKUP available in Excel 2019?
No. XLOOKUP is supported in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or Excel 2016. For those older versions, INDEX and MATCH is a common alternative.
Related
- Excel Get Data From a Semantic Model: Connect and Analyze
- Excel Data Model: What It Is and How to Use It
- Excel Data Model: Can You Turn It Off?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFS vs COUNTIF: When to Use EachIn Excel, IFS tests conditions in order and returns a corresponding result, while COUNTIF counts cells that meet…
- Excel Date Range Between Two Dates: Formulas and ExamplesTo calculate the number of days between two dates in Excel, subtract the earlier date from the later…
- How to Copy a Formula in Excel Across Multiple RowsTo copy a formula down rows in Excel, enter it in the first cell, then drag the fill…
- VLOOKUP Across Sheets in Excel: Formulas and FixesTo use VLOOKUP across sheets in Excel, enter a formula such as =VLOOKUP(A2,Products!$A$2:$D$100,3,FALSE) on the sheet where you…
- XLOOKUP Examples: Exact, Approximate, and Wildcard MatchXLOOKUP’s match_mode argument controls whether Excel finds an exact value, a nearby value, or a wildcard pattern. For…
- Excel Table Filter Button Greyed Out? Fixes That WorkIf Excel’s filter button is greyed out, first press Esc to leave cell-edit mode, ungroup any selected worksheets,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.