XLOOKUP Excel Criteria Match: Exact and Multiple Criteria

Formulas & functions · Updated November 18, 2026

Yes. XLOOKUP can match a criterion exactly by default, and it can also search with approximate or wildcard rules. For multiple criteria, combine logical tests into an array and look for 1; for example, with names in A2:A100, regions in B2:B100, sales in C2:C100, the name to find in E2, and the region in F2, use =XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"No match",0).

What XLOOKUP means by a criteria match

A criteria match is a test that determines which row should supply the answer. A simple lookup usually has one criterion, such as a product code, employee name, or invoice number. More involved lookups can require two or more conditions, such as finding a product in a particular warehouse or a person with a specific job title in a department.

XLOOKUP separates the search values from the returned values. Its first argument is the value to find, its second is the range or array to search, and its third is the range or array to return a result from. Optional arguments control what happens when there is no match, what kind of match to accept, and which direction to search. This structure makes XLOOKUP more flexible than older VLOOKUP formulas, but it does not automatically interpret several separate criteria: you have to build the combined test yourself.

The formula in the opening example tests every row for both the name and the region. Multiplying the two TRUE/FALSE tests works like an AND: a row produces 1 only when both tests are TRUE. XLOOKUP then finds 1 in that calculated array and returns the corresponding value from column C.

How exact and approximate matching work

In Microsoft 365 for Windows, XLOOKUP uses exact matching by default. Its match_mode argument is optional, but writing 0 explicitly can make an important formula easier to understand. Exact matching is normally the right choice for identifiers such as SKU codes, account numbers, email addresses, and category labels. It avoids returning a nearby value just because the exact item is absent.

With product codes in A2:A100 and stock quantities in B2:B100, and a code to find in E2, this formula returns the quantity for an exact code or a clear message if the code is absent:

=XLOOKUP(E2,A2:A100,B2:B100,"Code not found",0)

For a concrete example, if A8 contains P-204 and B8 contains 17, entering P-204 in E2 returns 17. If the code is not in the search range, the fourth argument displays Code not found instead of the default #N/A error.

The match_mode choices are:

  • 0: exact match. This is the default.
  • -1: exact match, or the next smaller item.
  • 1: exact match, or the next larger item.
  • 2: wildcard match, which recognizes wildcard characters such as * and ? in the lookup value.

Approximate matching is useful for ordered bands, such as a commission table or a grading scale, but it requires care. Think through what should happen between listed breakpoints and choose -1 or 1 accordingly. Do not use approximate matching as a shortcut for a messy text lookup: it can return a valid-looking result that is not the intended one.

Matching more than one condition with AND

To require every condition to be true, multiply the logical tests. In Excel, comparisons such as A2:A100=E2 produce arrays of TRUE and FALSE values. Multiplying two such arrays coerces TRUE to 1 and FALSE to 0, so only rows where both tests are true become 1.

For an example, assume A2:A100 contains employee names, B2:B100 contains departments, C2:C100 contains extension numbers, E2 contains the requested name, and F2 contains the requested department. If the matching employee is on row 12 and C12 is 4381, this formula returns 4381:

=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"No match",0)

Every array in the formula must cover the same rows. If one test checks A2:A100 while another checks B2:B99, their array dimensions differ and the formula can return an error. Also check that the return range aligns with those same records. A formula can be syntactically correct and still return the wrong result if one range starts a row too early.

You can extend the AND pattern with another condition. Suppose column C contains employee type, D contains extension numbers, and G2 contains the requested type. With names in A2:A100, departments in B2:B100, types in C2:C100, extensions in D2:D100, and the return value in D, use:

=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2)*(C2:C100=G2),D2:D100,"No match",0)

This returns the first extension whose name, department, and type all match. It does not report every qualifying record; XLOOKUP is a single-result lookup.

Using OR conditions and partial text

Sometimes a match should satisfy one condition or another rather than both. Addition combines logical tests as an OR, and testing whether the result is greater than zero creates a TRUE/FALSE lookup array. For example, with product names in A2:A100, categories in B2:B100, prices in C2:C100, and a name in E2 or category in F2, the formula below returns the first price matching either test:

=XLOOKUP(TRUE,((A2:A100=E2)+(B2:B100=F2))>0,C2:C100,"No match",0)

Use this pattern only when either condition is genuinely sufficient. If a row meets both tests, it still appears as one qualifying row; XLOOKUP returns the first qualifying row in the search order.

For a text value that contains a phrase, use wildcard mode. With descriptions in A2:A100, matching codes in B2:B100, and the search phrase in E2, the following finds the first description containing that phrase and returns its code:

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"No match",2)

The asterisk means any number of characters. A question mark matches one character. Wildcards are useful for descriptions that include extra text, but they can also make a search too broad. If E2 contains the phrase bolt, a description such as replacement bolt kit can match. If the search text itself includes wildcard characters and you need those characters treated literally, wildcard matching may not be appropriate; use an exact test or clean the search value instead.

Case sensitivity, duplicates, and search order

Standard XLOOKUP text matching is not case-sensitive. Searching for north can match North. If capitalization is part of the distinction in your data, use EXACT to create a case-sensitive test. With labels in A2:A100, results in B2:B100, and the exact capitalization to find in E2, this returns the result for the first case-sensitive match:

=XLOOKUP(TRUE,EXACT(A2:A100,E2),B2:B100,"No match",0)

XLOOKUP normally searches from the first item to the last and returns the first match. If the same key appears several times, later matches are ignored unless you change the search direction. With IDs in A2:A100 and status values in B2:B100, searching from bottom to top returns the most recent matching row when the data is arranged chronologically:

=XLOOKUP(E2,A2:A100,B2:B100,"No match",0,-1)

The final -1 here is the search_mode argument, not the match_mode argument. The formula still requires an exact match; the last argument changes direction. This distinction matters because the two optional arguments accept similar-looking numbers but control different behavior. XLOOKUP also offers binary search modes 2 and -2, but those are intended for data sorted in the required order. Using them on unsorted data can produce incorrect results, so the ordinary search modes are safer unless the sort order is controlled.

Returning more than one column

The return_array can contain multiple adjacent columns. If a row has a matching product code in A2:A100 and its description, price, and supplier are in B2:D100, XLOOKUP can return all three fields from the matching row. In Microsoft 365, the results spill into neighboring cells when those cells are empty. This is often simpler than writing separate lookups for each field.

Make sure the cells where the returned values need to spill are clear. If something blocks the output, Excel displays a #SPILL! error. A spill range also cannot expand into occupied cells, so check for stray values or merged cells near the formula before changing the lookup itself.

A related blank-cell detail can surprise users: if XLOOKUP finds a row whose return cell is blank, the displayed result may be 0. The not-found argument only handles the absence of a matching lookup value; it does not mean “show this text when the matched result is blank.” If blank returns have meaning in your data, decide whether to preserve, replace, or separately test for them.

Common errors and practical checks

If a formula returns #N/A, XLOOKUP did not find a match under the criteria supplied. Check for leading or trailing spaces, inconsistent number-versus-text storage, hidden characters, misspellings, and criteria that refer to the wrong cells. A code that looks like 00123 may be stored as text in one place and as the number 123 in another. Formatting a cell to display leading zeros does not necessarily change the underlying value.

If a multiple-condition formula returns an error, inspect the ranges first: the criteria arrays and return array need matching dimensions. For an unexpected result, confirm whether the data contains duplicates and remember that the first qualifying row wins by default. These practical checks often resolve problems more quickly than rewriting a working formula.

  • Use an explicit not-found message when a missing match is a normal possibility.
  • Keep lookup and return ranges the same height and aligned to the same records.
  • Use 0 for exact matching when clarity matters, even though it is the default.
  • Check whether duplicate keys are legitimate before relying on the first match.
  • Use wildcard mode only when partial text is genuinely intended.

Availability and how XLOOKUP compares

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including current Excel versions on Windows, Mac, and the web. It is not available as a built-in worksheet function in Excel 2019 or Excel 2016. A workbook containing XLOOKUP may show #NAME? in an older version that does not recognize the function. If colleagues need to open the file in older Excel, confirm their version or consider a compatible alternative.

Compared with VLOOKUP, XLOOKUP can search in either direction, returns results from a separate return range without a column index number, and has a built-in not-found argument. It also avoids the requirement that the lookup column be the leftmost column of a table. Compared with INDEX and MATCH, it often makes a one-criterion lookup shorter, while INDEX and MATCH remain useful for older Excel compatibility and some specialized layouts. For multiple criteria, all of these approaches need a deliberate method for combining conditions; XLOOKUP does not automatically infer that two columns should be tested together.

Frequently Asked Questions

Does XLOOKUP do exact match by default?
Yes. XLOOKUP uses exact matching unless you specify another match_mode. You can include 0 as the fifth argument to make exact matching explicit.

How do I use XLOOKUP with two criteria?
Multiply the two logical tests, look for 1, and return the corresponding range. For instance, the opening formula requires both the name and region to match. Ensure all criteria ranges and the return range cover the same rows.

Can XLOOKUP return the last matching result?
Yes. Set search_mode to -1 to search from last to first. This is useful when repeated records are ordered so the latest one is at the bottom. It changes the search direction, not the exact or approximate matching rule.

Is XLOOKUP case-sensitive?
No, its normal text match is case-insensitive. For a case-sensitive comparison, use EXACT to build the lookup array and search for TRUE.

Why does XLOOKUP return the wrong row when I have duplicates?
By default, it returns the first matching row from top to bottom. Check the source for duplicate keys, then decide whether the first record, last record, or a different rule is appropriate. Use search_mode -1 for the last match when the row order supports that choice.

Why can’t Excel find my value even though it looks identical?
The values may differ underneath because one is stored as text and the other as a number, or because a cell contains spaces or hidden characters. Compare the source values, normalize the data where needed, and make sure the criteria refer to the intended cells.

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.