XLOOKUP Array Formula in Google Sheets: How It Works

To return a matching result for every lookup key in Google Sheets, put the keys in a column and use =ARRAYFORMULA(IF(A2:A="", "", XLOOKUP(A2:A, D2:D, E2:E, "Not found"))). Here, lookup keys are in A, the search list is in D, and the values to return are in E; the formula leaves blank input rows blank and displays “Not found” when there is no match.

What an XLOOKUP array formula does

A regular XLOOKUP finds a value in a one-dimensional range and returns the value at the corresponding position in another range. An array formula applies that lookup to multiple keys, so one formula can populate a set of results rather than requiring a separate lookup formula in every row.

In Google Sheets, XLOOKUP can return results for an array of search keys. Sheets also expands many array results into adjacent cells automatically. Wrapping the expression in ARRAYFORMULA makes the intended column-wide calculation explicit, but the wrapper is not always necessary for XLOOKUP. If you are looking up just one key, use an ordinary XLOOKUP formula instead.

The ranges need to line up. The lookup range must be one row or one column, and the result range must have the same orientation and corresponding number of positions. For example, searching down a product-code column and returning a product-name column works; searching a vertical list and trying to return from an unrelated horizontal range does not.

How the formula works

In the opening example, A2:A supplies the search keys, D2:D is the list in which Sheets searches, and E2:E contains the results to return. XLOOKUP compares each key with the lookup list and returns the value in the matching position. The fourth argument, “Not found,” is the optional result to show when a key has no match.

ARRAYFORMULA allows the calculation to apply down the ranges rather than stopping at the first row. The IF condition checks each cell in column A first. If that cell is empty, it returns an empty string instead of trying to look up a blank key. This matters with open-ended ranges such as A2:A: without the blank check, the formula could produce unwanted results or errors in rows where no key has been entered.

Place the formula in the first cell where you want results to appear, such as B2, and leave the cells below it clear. The results expand down the column from that starting point. You do not copy the formula into every row; adding more keys in column A lets the existing formula process those rows.

A practical product lookup example

Suppose column A contains requested product codes, column D contains the product catalogue codes, and column E contains product names. With this layout, put the formula in B2:

With requested codes in A2:A, catalogue codes in D2:D, and product names in E2:E, use this formula in B2:

=ARRAYFORMULA(IF(A2:A="", "", XLOOKUP(A2:A, D2:D, E2:E, "Not found")))

If A2 contains “SKU-104” and that code is in D7, the result in B2 is the product name from E7. If A3 contains a code that is absent from column D, B3 displays “Not found.” If A4 is empty, B4 remains visually blank. The specific product name depends on the data in the sheet; XLOOKUP returns the value from the row that matches the key.

If you need a single lookup rather than an array, use a single-cell reference as the search key. For example, with the code to find in G2, catalogue codes in D2:D and names in E2:E:

=XLOOKUP(G2, D2:D, E2:E, "Not found")

This is often easier to understand in a small table where each row has its own formula. The array version is useful when you want one formula to manage a whole output column and avoid dragging formulas down as the source list grows.

Match options and duplicate values

By default, XLOOKUP looks for an exact match, which is generally what you want for IDs, email addresses, product codes, and names. Its optional match mode can change that behavior: exact or next greater, exact or next smaller, or wildcard matching. Approximate matching should be chosen deliberately, particularly when looking up thresholds or ranges, because an incorrectly chosen direction can return a plausible but wrong result.

Search mode controls the direction of the search. The default searches from the first item to the last. A reverse search can be useful when a code appears more than once and you want the last matching entry rather than the first. XLOOKUP returns one matching result for each search key; it is not a tool for listing every row that meets a condition. Use FILTER when the goal is to return all matching rows.

Wildcard matching can find text containing a phrase, but it does not mean “contains” unless you add wildcard characters around the search text. With the phrase in G2, names in D2:D, and corresponding results in E2:E, this formula uses wildcard match mode:

=XLOOKUP("*"&G2&"*", D2:D, E2:E, "No match", 2)

That example returns the first value associated with a name containing the text in G2. Wildcard behavior is intended for text searches; for ordinary product codes, exact matching is clearer and less likely to match an unintended item.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

Compared with VLOOKUP, XLOOKUP does not require the lookup column to be the first column in the selected table. You specify the lookup range and return range separately, so the return values can be to the left or right of the search values. This can make a formula easier to maintain if columns are rearranged.

INDEX and MATCH can also perform flexible lookups, but they use two functions and can be harder for a new Sheets user to read. XLOOKUP puts the search key, lookup range, and result range together in one function. VLOOKUP may still be familiar to collaborators or useful in existing spreadsheets, while FILTER is the better fit when one key should return multiple matching records.

These functions are not interchangeable in every situation. XLOOKUP returns a corresponding result for a match; it does not automatically sort data, remove duplicates, or return all matching rows. Choose the function based on the shape of the result you need, not just on which formula looks shortest.

Common errors and how to fix them

Results do not expand: Clear any values, formulas, or merged cells in the cells where the output needs to go. An array result cannot overwrite existing content. Also check that the formula is entered in only the intended starting cell.
A missing key returns an error: Supply the optional missing-value argument, such as “Not found,” to show a readable message rather than the default not-found error. Check spelling, spaces, and whether the lookup values are stored consistently as text or numbers.
Unexpected blank results: A key may match a row whose return cell is blank, or the formula’s IF condition may intentionally be suppressing output for a blank input. Inspect the corresponding return-range cell before assuming XLOOKUP failed.
The wrong duplicate is returned: The default search begins at the start of the lookup range, so the first duplicate wins. Choose a reverse search mode if the last occurrence is the one you need, or clean up duplicates if a key should be unique.
The formula is slow: Open-ended whole-column references are convenient, but very large or complicated sheets may calculate more efficiently with bounded ranges. Keep the lookup and result ranges aligned when you limit them.
Range-size or orientation problems: Make sure the lookup and result ranges each run in the same direction and cover corresponding entries. A one-column lookup paired with a result range of a different length can cause errors or incorrect output.

Good practices for a maintainable sheet

Use clear headers and keep lookup keys consistent. A code that is numeric in one table and stored as text in another can fail to match even when the displayed characters look alike. Leading or trailing spaces can also interfere. When a lookup is important to a report, test it with a known key, an unknown key, a duplicate if duplicates are possible, and a blank input.

Consider whether “Not found” is the right fallback. It is easy to spot while reviewing a sheet, whereas a blank fallback can make missing data look like an intentional empty result. Conversely, if the output feeds another calculation, a text message may not be suitable; choose a fallback that makes sense for that downstream use.

Google Sheets runs in a browser and updates its functions independently of desktop Excel. Excel also has XLOOKUP, but its array calculation behavior and worksheet features can differ by version. If a sheet will be shared between Google Sheets and Excel, verify the formula and resulting layout in both applications rather than assuming every spreadsheet feature behaves identically.

Frequently Asked Questions

Do I need ARRAYFORMULA with XLOOKUP in Google Sheets?
Not in every case. XLOOKUP can return multiple results for array search keys, and Sheets can expand array results automatically. ARRAYFORMULA makes a whole-range calculation explicit, but a single-key lookup does not need it.

How do I make XLOOKUP return several rows in Google Sheets?
Provide a range of search keys instead of a single cell, and ensure the cells below the formula are clear for the results to expand. If you want every source row that meets a condition, rather than one result per search key, use FILTER instead.

How do I return a blank when XLOOKUP finds no match?
Use an empty string as XLOOKUP’s optional missing-value argument. For a whole column, you can also add an IF test so blank search-key cells stay blank rather than being looked up.

Can XLOOKUP return a value from a column to the left?
Yes. In Google Sheets, the lookup range and result range are separate arguments, so the result range can be positioned to the left or right of the lookup range. Make sure the two ranges still correspond item by item.

Why does my XLOOKUP array formula say it cannot expand?
One or more cells in the output area may already contain data, or merged cells may obstruct the array result. Clear the required output cells and check that the formula is not competing with another formula in the same spill area.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.