XLOOKUP in Excel: matching, missing codes and search direction

VLOOKUP expects a key in the first column of its range and a column number for the answer. With XLOOKUP, I specify the lookup range and the return range separately. That lets me retrieve a name to the left of a product code, a price to its right or a value in a horizontal layout.

The Polish function name is X.WYSZUKAJ, including the dot after X. I will use a synthetic product catalog and a separate threshold table to explain the arguments. The important choices concern missing matches, duplicate keys and the direction of the search.

Availability and the complete syntax

XLOOKUP is available in Microsoft 365, Excel 2021 and 2024, and Excel for the web. It is not available in Excel 2016 or 2019. The Microsoft documentation explains these limitations. Before sharing a workbook, I check which version its recipient uses. A formula working on my computer does not settle compatibility for everyone else.

There are three required arguments followed by three optional ones:

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

First comes the value to find, then the key range and the answer range. The fourth argument supplies a result for a missing match. The fifth chooses the matching rule, and the sixth selects the search direction or method. Leaving the missing-match argument out of a syntax explanation shifts the meaning of the remaining numbers.

Examples below use English names and comma separators. Regional settings can require semicolons instead. Square brackets in the syntax description indicate optional arguments; you do not enter those brackets around the values in an actual formula.

Start with three products

Enter this synthetic catalog in A1:C4. Names are to the left of codes, while prices are to the right. Codes are text and prices are numbers.

Enter P-20 in E2 and put the following formula in F2:

=XLOOKUP(E2,$B$2:$B$4,$A$2:$A$4)

The result is “Binder.” Excel finds P-20 at the second position in B2:B4 and returns the second position from A2:A4. I do not need to rearrange columns, even though the answer is to the left of the key. Changing the return range to C2:C4 retrieves the price, 18.

In Polish Excel, with semicolon separators, the first formula is:

=X.WYSZUKAJ(E2;$B$2:$B$4;$A$2:$A$4)

This basic call uses an exact match and searches from the beginning. You do not need to add the equivalent of FALSE from VLOOKUP. An explicit zero in the fifth argument can nevertheless help somebody understand a longer formula.

Range alignment matters more than location

Lookup and return ranges can be in different parts of a worksheet. Their positions must still correspond. In a vertical dictionary, the first lookup position needs to correspond to the first return row, the second to the second and so on.

If you select B2:B4 and A3:A5, both ranges have the same length but their rows are shifted. The match for P-20 will retrieve A4 instead of A3. Excel does not understand the business meaning of the catalog and cannot warn that you paired a binder code with a folder name. Matching dimensions alone do not establish correct alignment.

I therefore select both ranges carefully and inspect the first and last row. When copying down, I lock the ranges with dollar signs while leaving E2 relative. An Excel table can make column references easier to maintain, but named columns still need to represent the right relationship.

Choose a useful missing-match result

For P-99, the basic call returns #N/A. If an unknown product is an expected case, I can add a message as the fourth argument:

=XLOOKUP(E2,$B$2:$B$4,$C$2:$C$4,"Missing code")

P-20 still returns 18, whereas P-99 returns “Missing code.” This argument handles the absence of a matching key. It is not a general instruction to hide every error. An error stored in the matched return cell or an invalid range needs separate attention.

I do not automatically use zero here. If zero is a valid price, an unknown code would become indistinguishable from a free product. An empty string can also conceal missing information from the reader. For later calculations, a separate status column is often clearer than a single column containing both numbers and messages.

A missing key is different from a blank value in a matched row. Finding P-20 does not establish that its price is complete. The catalog owner should decide whether blank prices are allowed and how the report should display them.

First and last matches

By default, the search starts at the first position. When a key repeats, you get the first match found. A sixth argument of -1 reverses the search:

=XLOOKUP(E2,$B$2:$B$4,$C$2:$C$4,"Missing code",0,-1)

This produces the same result on the original table because each code appears once. To see the difference, add a second P-20 with price 19 in row 5 and extend both ranges to row 5. The ordinary search returns 18; a reverse search returns 19.

The last position is not necessarily the latest event. If the source is not ordered by time, -1 selects the last row in the range rather than the row with the greatest date. Before retrieving a current price, define the effective-date rule. Then decide whether the chosen search direction actually implements it.

Match modes: zero, minus one and one

The fifth argument controls what happens when there is no exact lookup value. Zero requests an exact match. Minus one requests an exact match or the next smaller value. One requests an exact match or the next larger value. Keep these separate from the sixth argument that controls how the range is searched.

Create a synthetic table in H1:I4. Lower quantity thresholds are 0, 10 and 20, with classes Small, Medium and Large. Enter 14 in K2. A match mode of -1 selects threshold 10:

=XLOOKUP(K2,$H$2:$H$4,$I$2:$I$4,"Outside range",-1)

The result is Medium. For 20, the result is Large; for 9, it is Small. A table of upper bounds describes a different classification and may require mode 1. I do not switch between modes until the number looks better. I define the intervals first.

With the ordinary search method, these approximate match modes do not require the sorting used by approximate VLOOKUP. I still arrange thresholds in ascending order for readability. Sorting becomes mandatory for the separate binary search modes in the sixth argument.

Binary search has a prerequisite

A sixth argument of 2 performs a binary search on an ascending lookup array. Minus two expects descending order. Microsoft warns that incorrectly sorted data can produce invalid results. These options therefore come with a source-data requirement.

For a tiny catalog, I have little reason to complicate the formula. I leave the default search method in place unless there is a specific problem and a reliable ordering guarantee. I also avoid unsupported speed promises: calculation time depends on the workbook, the number of formulas and the sizes of the referenced ranges.

If other people can insert source rows anywhere, maintaining the required order can become difficult. The condition should be visible to the process owner rather than hidden inside one formula argument. A correct answer today does not guarantee a correct answer after the next import.

Wildcards and literal identifiers

A fifth argument of 2 enables wildcard matching. An asterisk represents a sequence of characters, a question mark represents one character, and a tilde escapes a special character. This is an explicit choice beyond the default exact-match mode.

In our catalog, looking for P-2* in wildcard mode finds P-20. If several codes begin with P-2, you receive one result according to the search direction. The formula does not generate a list of every matching product.

I would avoid this method for retrieving prices when the identifier must be unique. For multiple results, choose a filtering tool. Pattern matching can help explore a dictionary, but finding a shared text fragment does not prove that two records describe the same product.

Return several columns with one formula

The return range can contain several adjacent columns. Go back to the original A1:C4 catalog and place this formula outside its area, for example in F2:

=XLOOKUP(E2,$B$2:$B$4,$A$2:$C$4)

For P-20, the result occupies F2:H2: Binder, P-20 and 18. There is still one matched row, but it supplies three returned fields. Cells to the right must be available for the result to spill.

The Microsoft guide to spilled arrays explains how neighboring cells receive the results. Put a spilling formula outside an Excel table; the source ranges can still be table columns. If spilling fails, inspect occupied or merged cells rather than hiding the error behind an empty string.

Horizontal lookup uses the same principle

On a separate worksheet, enter P-10, P-20 and P-30 in B1:D1. Enter prices 12, 18 and 7 in B2:D2. Looking for P-20 in the first row and returning the corresponding item from the second produces 18:

=XLOOKUP("P-20",B1:D1,B2:D2)

Check the orientation of both arrays. Horizontal positions correspond to columns, while vertical positions correspond to rows. The alignment rule applies in both cases. The function does not infer whether your report was rotated or where its true header begins.

This is helpful for reading an existing cross-tab, but I would still consider a row-based table for a growing source. Adding periods as new columns can make downstream processing harder to maintain. Choosing a lookup orientation does not replace the decision about a useful source structure.

Prepare the dictionary before copying formulas

I inspect the key’s data type, completeness and uniqueness. An identifier containing leading zeros stays text. If the source distinguishes uppercase from lowercase, I remember that ordinary XLOOKUP does not enforce that distinction.

Before filling the result column, I compare several source-and-result pairs. I include the first, middle and last positions plus an absent code. Then I inspect a repeated code and decide whether the first or last answer makes business sense. Those cases reveal errors that simply opening the workbook will not expose.

If I need a total across many product transactions, I turn to SUMIFS. XLOOKUP retrieves a matched answer; it does not automatically add every row sharing the key. This difference matters when dictionaries and transaction lists have similar-looking columns.

Keep the formula understandable during handover

I label the returned field with its meaning. “Catalog price” helps distinguish it from the price actually charged. I also explain what the missing-code message requires somebody to do: update the dictionary, correct the source identifier or review the transaction.

If a formula is copied into a monthly report, I check that it still points to the intended period’s dictionary. Keeping the old worksheet name can silently retain last month’s prices. Named columns improve readability, but they do not determine which version of the source is appropriate.

When I replace formulas with saved values, I label the snapshot date. The resulting report will no longer respond to catalog updates. That can be the right outcome for an archived statement, provided its reader understands the distinction.

Exercise with five situations

Using the original catalog, retrieve the names for P-10 and P-30, the price for P-20 and a message for P-99. Expected results are Notebook, Folder, 18 and your chosen missing-code text. Next, add another P-20 at price 19, extend the ranges and compare search directions.

On the separate threshold table, inspect 9, 10, 19 and 20. Choosing the next smaller threshold should produce Small, Medium, Medium and Large. Explain each assignment without looking at the formula. If the interval definition is unclear on paper, the worksheet cannot resolve it.

Finally, record who maintains the dictionary and when it was updated. A function can accurately retrieve an outdated price. A useful report needs a current source and an explicit matching rule as well as a valid formula.