XLOOKUP Examples With Arrays in Excel

With product names in A2:A6, prices in B2:B6, and a name to find in D2, use =XLOOKUP(D2,A2:A6,B2:B6,"Not found"). XLOOKUP searches the lookup array and returns the corresponding value—or, when the return array contains several columns or lookup values, a spilled array of results.

What arrays mean in XLOOKUP

In XLOOKUP, an array is a set of values arranged in one or more rows or columns. You will most often use worksheet ranges, such as A2:A20, rather than typing an array directly into a formula. The lookup array contains the values to search, while the return array contains the values to bring back. These arrays must line up: the position of a match in the lookup array determines the position returned from the return array.

That position-based design is why XLOOKUP can search in either direction. Unlike VLOOKUP, it does not require the lookup column to be the leftmost column in a table, and you do not have to count a return-column number. It can also return a whole row or several adjacent columns when its return array is wider than one column.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Its dynamic-array results spill into neighboring cells automatically. Older Excel versions that do not support XLOOKUP may display an unrecognized-function error, or may show a compatibility prefix when opening a workbook created in a newer version.

How lookup and return arrays work

The function’s syntax is XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are the essentials. The remaining arguments let you provide a custom not-found result, choose an exact, approximate, or wildcard match, and control the direction or method of the search.

For a basic vertical lookup, the lookup array and return array usually have the same number of rows. They can be different widths if the return array contains multiple columns, but the records still need to correspond in the same row order. For a horizontal lookup, use rows instead of columns. If the arrays do not align, a formula can return an error or match the wrong record because values are associated by position, not by a separate row identifier.

XLOOKUP returns the first matching item by default. Exact matching is also the default, so a text value must match the lookup entry rather than merely contain it. If you need the last matching item, a wildcard match, or an approximate match, set the optional arguments deliberately.

Example: return several columns

Suppose A2:A6 contains product names, B2:B6 contains departments, and C2:C6 contains prices. D2 contains the product to find. Because the return array below includes both B and C, the formula returns a two-column result. For example, with Notebook in D2 and Stationery and 2.50 in the matching row, the results are Stationery and 2.50 in adjacent cells.

With product names in A2:A6, departments in B2:B6, prices in C2:C6, and the requested product in D2, use:

=XLOOKUP(D2,A2:A6,B2:C6,"Not found")

The result spills from the formula cell into the next column, so keep that destination cell empty. If the spill area contains existing data, Excel reports a #SPILL! error rather than overwriting it. This multi-column return is useful for bringing back a record’s category, price, and stock status at once; it does not require separate lookup formulas for each field.

Example: look up several values at once

The lookup value can itself be a range of values. XLOOKUP evaluates each item and returns a matching result for each one, creating a vertical spill when the input range is vertical. For instance, if G2:G4 contains three product names, the formula below returns their prices in three corresponding rows. If the names are Notebook, Pen, and Folder, and their prices in the source table are 2.50, 1.25, and 3.00, those are the three returned values.

With product names in A2:A6, prices in B2:B6, and the requested names in G2:G4, use:

=XLOOKUP(G2:G4,A2:A6,B2:B6,"Not found")

Enter the formula once, normally in the first output cell, rather than copying it down. The result expands as a dynamic array. Make sure the spill range is clear, and do not type into the cells where the results need to appear. If a source value is missing, the corresponding result is the text Not found, while successful lookups still return their prices.

Example: match on multiple criteria

To find a record using two conditions, build a temporary array of TRUE and FALSE results from the criteria. Multiplying the tests converts TRUE to 1 and FALSE to 0; only rows where both tests are TRUE produce 1. XLOOKUP then searches for 1 in that calculated array and returns the value from the corresponding row.

Assume A2:A7 contains employee names, B2:B7 contains departments, and C2:C7 contains hours. Put the target name in G2 and department in H2. If the matching employee and department occur together on a row with 38 hours, this formula returns 38:

=XLOOKUP(1,(A2:A7=G2)*(B2:B7=H2),C2:C7,"No match")

This is an AND condition: both tests must be true on the same row. It is not the same as searching for a combined text key, and it avoids needing to add helper columns. If multiple rows satisfy the criteria, the default search returns the first one. To return the last matching record instead, you can set the search mode to -1, provided you still want the last match in the current row order.

Example: search across a row

XLOOKUP works horizontally as well as vertically. This is helpful when months, product names, or other labels appear across columns. The lookup array is then a single row, and the return array is the corresponding row of values. With month names in B1:M1, sales in B2:M2, and the month to find in O1, the formula returns the sales for that month. If O1 contains March and March sales are 12,400, the result is 12,400.

With months in B1:M1, sales in B2:M2, and the requested month in O1, use:

=XLOOKUP(O1,B1:M1,B2:M2,"Month not found")

The matching positions must correspond: the value in B2 belongs to the label in B1, C2 to C1, and so on. XLOOKUP does not require the matching label to be at the beginning of the row. For a two-way lookup—choosing a row label and a column label in a grid—nesting one XLOOKUP inside another is a common approach, because the inner lookup can select a row and the outer lookup can select a value from it.

Array constants and calculated return arrays

An array does not have to come from a cell range. In a small demonstration, you can type values directly into an array constant inside a formula. In English-language Excel, commas separate columns in a horizontal array constant, and semicolons separate rows. Regional settings can affect which separators Excel expects, so a formula copied from another locale may need adjustment.

This formula searches a three-item horizontal array and returns the value in the corresponding position. It returns 20 for B:

=XLOOKUP("B",{"A","B","C"},{10,20,30},"Not found")

Array constants are convenient for compact examples or fixed mappings, but they are awkward to maintain when the list grows. A worksheet range is usually easier to update and audit. You can also calculate a return array instead of returning stored values unchanged. For example, multiplying a price range by 1.1 returns values increased by ten percent. If the matching stored price is 2.50, the returned value is 2.75.

With product names in A2:A6, prices in B2:B6, and the requested product in D2, use:

=XLOOKUP(D2,A2:A6,B2:B6*1.1,"Not found")

This example performs arithmetic on the return array; it does not alter the source prices. Keep in mind that the added amount is a mathematical increase, not a special tax or discount calculation. Use the correct business rule for your data.

Choosing match and search options

Exact match is normally the safest choice for IDs, names, and category labels, and it is XLOOKUP’s default. When you need another behavior, the fifth argument controls match mode and the sixth controls search mode. They are separate settings: match mode describes what counts as a match, while search mode describes how Excel searches the array.

Exact: match mode 0, or omit the argument. This is the default.
Exact or next smaller: match mode -1. This can be useful for threshold tables when the lookup values are arranged appropriately.
Exact or next larger: match mode 1, also useful for certain ordered thresholds.
Wildcard: match mode 2. This supports wildcard characters such as * for any number of characters and ? for one character.
Last-to-first search: search mode -1. This returns the last match in the array’s current order.

For example, if A2:A6 contains descriptions and B2:B6 contains codes, this formula finds the first description containing the text in D2. With descriptions in that range, codes in B2:B6, and a search fragment in D2, use:

=XLOOKUP("*"&D2&"*",A2:A6,B2:B6,"No match",2)

The asterisks allow other text before or after the fragment. Wildcard matching is not case-sensitive. Avoid binary search modes unless the lookup array is sorted as required; using binary search on unsorted data can produce incorrect results. For most ordinary lists, the default linear search is simpler and dependable.

Common errors and practical checks

A #N/A result means XLOOKUP did not find a match and no custom not-found result was supplied. Providing the fourth argument gives a clearer message, but it can also hide an unexpected mismatch, so investigate missing results when the data should match. Check for extra spaces, inconsistent number-versus-text formats, and small spelling differences. A number stored as text may look like a number but not behave like one in an exact lookup.

#SPILL!: clear cells in the expected output area. Multi-column returns and multiple lookup values need room to expand.
#VALUE! or an unexpected result: check that the lookup and return arrays are compatible and that the intended records occupy corresponding positions.
Wrong duplicate returned: XLOOKUP returns the first match by default. Use reverse search when the last occurrence is the intended result.
Formula not recognized: confirm that the workbook is being opened in Microsoft 365 or Excel 2021 and later.
Formula returns a surprising approximate match: inspect the match-mode argument. For ordinary identifiers, use exact matching rather than threshold-style matching.

When copying formulas, consider whether the lookup ranges should remain fixed. For instance, a formula filled down may need absolute references such as $A$2:$A$20 so the source ranges do not shift. In a spill formula that already looks up several values at once, copying it down is usually unnecessary and can create overlapping results.

Frequently Asked Questions

Can XLOOKUP return more than one column?
Yes. Use a multi-column return array, such as B2:C20, while searching a matching row range such as A2:A20. Excel spills the returned columns into adjacent cells if the output area is empty.

Can XLOOKUP search for two conditions?
Yes. Multiply logical tests to require both conditions, then search for 1 in the resulting array. Each test must cover the same rows, and the return array must align with those rows.

Does XLOOKUP return all matching rows?
No. A standard XLOOKUP returns one matching position for each lookup value, and returns the first match by default. For a list of every row meeting a condition, FILTER is generally a better fit in Microsoft 365 or Excel 2021 and later.

Can XLOOKUP work horizontally?
Yes. Use a single-row lookup array and the corresponding single-row return array. XLOOKUP can search from left to right or right to left without rearranging the table.

Why does XLOOKUP show #SPILL!?
Excel cannot place the complete array result because one or more cells in its output area are occupied, merged, or otherwise blocking the spill. Clear the affected cells or move the formula to an open area.

Is XLOOKUP case-sensitive?
No. Ordinary XLOOKUP text matching is not case-sensitive. If capitalization must distinguish otherwise identical entries, use a case-sensitive approach based on functions such as EXACT rather than relying on the standard match.

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.