XLOOKUP in Excel: exact matches, duplicates and search modes

You want to add stock levels to a product list. You have an identifier, while another table contains the name, department and quantity. I would start with XLOOKUP, but before copying the formula I would check whether each source identifier appears only once. A correctly functioning lookup can select the wrong record for the task if that rule remains unresolved.

XLOOKUP is called X.WYSZUKAJ in Polish Excel. It defaults to an exact match, searching from the first element. It is available in Microsoft 365 and Excel 2021 and 2024, but not Excel 2016 or 2019. Microsoft explicitly confirms that limitation, even though older versions can appear in the page’s general “Applies To” list.

The formulas below use English function names and commas. Argument separators depend on regional settings. All data is synthetic, and both language versions use the same product values. This is not a real store’s inventory. The repeated identifier and empty value are deliberate parts of the exercise, rather than mistakes to remove before starting.

Understand the six arguments

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

First comes the value, then the range to search and the range to return from. The fourth argument specifies the response when there is no match. The fifth selects matching rules, while the sixth selects direction or search algorithm. Square brackets in the syntax mark optional arguments; do not type them into your working formula.

I separate two questions: what qualifies as a match, and where does the search begin? A -1 in the fifth argument means exact match or the next smaller value. The same number in the sixth argument means starting from the end of the range. Swapping those positions changes the operation, rather than merely changing how the formula is written.

Prepare a small control table

Enter the headers in A1:D1 and the six records below in A2:D7. Leave D7 genuinely empty; do not type “blank” into it. P-102 is repeated deliberately so we can compare the first and last matches.

The source represents product stock rather than sales events. If the two P-102 records represented different warehouses, you would need to include warehouse in the key or aggregate the relevant records. Selecting the first row does not resolve the definition. Here, I treat row order only as a property of the table, without pretending it represents timestamps.

Exact matching is the default

=XLOOKUP("P-102",$A$2:$A$7,$D$2:$D$7)

The result is 25, from the first P-102 record. You do not need to add 0 to enable exact matching, and this ordinary search does not require sorted data. Dollar signs hold the ranges in place when the formula is copied. In a working sheet, you can replace the literal identifier with a cell reference.

I check that the ranges cover corresponding rows. Searching A2:A7 but returning D3:D8 shifts the relationship by one record, although both ranges have the same length. This can be harder to notice than an error message. I therefore begin with a product whose answer I can identify manually in the source before applying the formula across a longer list.

A missing match does not mean zero

=XLOOKUP("P-999",$A$2:$A$7,$D$2:$D$7,"Missing")

The result is Missing. Without the fourth argument, an unmatched identifier produces #N/A (#N/D in Polish Excel). Initially, I prefer an explicit message to zero because zero stock is a valid answer for P-104. Hiding both situations under the same number makes completeness checks harder.

The fourth argument handles a missing match rather than every possible formula problem. It does not repair incorrect ranges or an error in a matched result cell. If broader error handling is needed, establish the cause first. Wrapping the whole calculation in IFERROR without understanding the problem can hide a defect in the source and make the output appear more complete than it is.

Looking left and searching backwards are different

Looking left means the result column is before the lookup column. Here, I search product names in B and return identifiers from A:

=XLOOKUP("Notebook",$B$2:$B$7,$A$2:$A$7)

The result is P-102. This genuinely demonstrates returning a column to the left. Searching from the end instead changes the order in which records are considered:

=XLOOKUP("P-102",$A$2:$A$7,$D$2:$D$7,"Missing",0,-1)

The result is now 31. The sixth argument, -1, selects the last matching record in the range. That does not automatically mean the latest measurement or the largest number. Rearranging rows can change the answer. If you need the newest record, establish a timestamp and a clear ordering rule, including how ties are resolved.

Return several columns from one record

=XLOOKUP("P-102",$A$2:$A$7,$C$2:$D$7)

Enter the formula in J2, for example, and leave K2 empty. It returns Office and 25 side by side. The return array contains two columns, so the result occupies two cells. It is still one matching record. The two occurrences of P-102 in the source do not cause both rows to be returned.

Microsoft’s dynamic-array documentation explains spilling and the need for unobstructed output space. A blocked range can produce #SPILL!. Place a spilling formula outside an Excel Table. If you need every matching record, consider FILTER; if you need a total across several records, use an appropriate aggregation instead of selecting one row.

Approximate matching needs a threshold rule

Create a second table in F1:G5. These numbers are lower boundaries of illustrative size bands, independent of the stock table.

=XLOOKUP(45,$F$2:$F$5,$G$2:$G$5,"Missing",-1)

The result is Medium because 20 is the largest available threshold not exceeding 45. An exact lookup value of 50 returns High. For a lookup value of -1, neither an exact match nor a smaller threshold exists, so the result is Missing. Checking this boundary is part of designing the threshold table rather than an optional final detail.

=XLOOKUP(45,$F$2:$F$5,$G$2:$G$5,"Missing",1)

This returns High, corresponding to the next larger threshold, 50. Choosing -1 or 1 should follow the interval definition. I do not change the sign merely because the other answer looks preferable. For categories defined by lower boundaries, I generally need the next-smaller variant. A table of upper limits would call for a different interpretation.

Enable wildcard matching explicitly

=XLOOKUP("Note*",$B$2:$B$7,$D$2:$D$7,"Missing",2)

The result is 25. A fifth argument of 2 enables wildcard matching: an asterisk matches a sequence of characters, a question mark matches one character and a tilde escapes a wildcard character. Without this mode, do not assume that text containing an asterisk automatically becomes a search pattern.

In practice, I check whether the pattern matches too many names. “Note*” could cover different products, while the function still selects one record according to search direction. I prefer a stable identifier for product matching. Patterns are useful when I deliberately search text and understand the ambiguity, rather than when I am trying to conceal inconsistent keys.

Binary search has a strict prerequisite

=XLOOKUP(45,$F$2:$F$5,$G$2:$G$5,"Missing",-1,2)

For our ascending threshold table, this returns Medium. A sixth argument of 2 means binary search requiring ascending lookup values; -2 requires descending values. Microsoft warns that an unsorted lookup array can return invalid results. I do not enable the mode without checking that prerequisite.

I sort related records together, rather than sorting only the key column. Otherwise, a threshold may become associated with the wrong band. For a small exercise, ordinary search is sufficient. I do not promise a performance improvement without measuring the actual workbook. Correctness matters more than choosing an algorithm with a more advanced-sounding name.

An empty value in a matching row

P-105 exists, but D7 is empty. The “Missing” argument does not apply because the lookup found a match. A straightforward lookup reference to an empty cell can display zero. To distinguish missing stock from the genuine zero for P-104, transform the return range explicitly:

=XLOOKUP("P-105",$A$2:$A$7,IF($D$2:$D$7="","",$D$2:$D$7),"Missing")

The result is empty text, "", rather than a physically empty cell. Using the same construction for P-104 returns the number 0; P-999 returns Missing. Those are three distinct situations. The presentation should preserve their meaning, particularly if the result feeds subsequent calculations or completeness checks. A visually blank output does not necessarily have the same properties as an untouched cell.

Inspect the key before rewriting the formula

When a match appears to be missing, I inspect the source value: extra spaces, numbers stored as text, invisible characters and identifiers with leading zeros. I do not remove every difference automatically. If “0012” is an identifier, converting it to the number 12 may destroy information needed for the intended relationship.

I also check key duplication and coverage. A new record added below A7 is outside the fixed range A2:A7. An Excel Table with structured references can help maintain an expanding source, but it does not remove the need to validate keys. For the older lookup approach and its limitations, see the VLOOKUP entry.

For practice, use P-103, P-104, P-105 and the nonexistent P-999. Check an ordinary value, genuine zero, empty text and a missing match. Then swap the two P-102 rows and compare searches from the beginning and end. This shows which rules belong to the function and which rules you need to establish for your own data.