Google Sheets ARRAYFORMULA with VLOOKUP: How It Works

In Google Sheets, combine ARRAYFORMULA with VLOOKUP to look up many values at once and return the results down a column. Put the formula in the first result cell, use a range of lookup keys, and set VLOOKUP’s last argument to FALSE for exact matches.

What ARRAYFORMULA and VLOOKUP do together

VLOOKUP searches for a value in the leftmost column of a table and returns a value from another column in the same row. By itself, a typical VLOOKUP formula handles one lookup key. ARRAYFORMULA lets a formula work with ranges, so a VLOOKUP can process a column of keys and return multiple results from a single formula.

This is useful for joining two lists. For example, one sheet might contain product IDs and order quantities, while a second sheet stores product IDs, names, and prices. Instead of writing a separate lookup beside every order, you can enter one formula and have it fill the result column automatically as the source list grows.

In Google Sheets, enter the formula in the top cell where you want the results to appear. The results expand into the cells below; you do not need to copy the formula down or use the old Ctrl+Shift+Enter array-entry method. Keep the cells where results need to appear empty, because existing content can prevent the array result from expanding.

A basic whole-column example

Suppose the current sheet has product IDs in A2:A. On a sheet named Products, IDs are in column A, product names in column B, and prices in column C. To return the prices beside the IDs, enter this formula in B2:

With product IDs in A2:A and a Products sheet laid out as ID in column A, name in B, and price in C, enter this in B2: =ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(A2:A, Products!A2:C, 3, FALSE)))

The formula checks each lookup cell first. If an ID cell is blank, it returns a blank rather than trying to look up an empty value. For a matching ID, VLOOKUP searches the first column of Products!A2:C and returns the third column of that range. That third column is column C, which contains the prices. If ID P-104 has a price of 12.50 in the product table, the corresponding result is 12.50.

The number 3 is the index within the selected table range, not the worksheet’s column number. If you change the table range to start in column B, its second column would be index 2, even if that column is column C on the sheet.

How to build the formula correctly

Set up the lookup table and result column before writing the formula. Then check the following parts of the expression:

Lookup range: The first argument to VLOOKUP is the set of values to search for. In the example, A2:A means each non-header product ID in column A.
Table range: The first column of this range must contain the IDs being searched. The return values must be in a column to its right.
Column index: Count from the left edge of the table range to the column you want returned. Use 1 for the lookup column itself, 2 for the next column, and so on.
Match setting: Use FALSE for an exact match. This is usually the right choice for IDs, names, and other lookup keys. With TRUE or an omitted final argument, VLOOKUP uses approximate matching, which has different requirements and can produce surprising results if the data is not sorted.
Output space: Put the formula in the first result cell and make sure cells below it are available for the returned values.

Use a bounded range such as A2:A500 when you know the likely data size, or an open-ended range such as A2:A when new rows are routinely added. Open-ended references are convenient, but very large ranges and complicated formulas can make a busy spreadsheet slower.

Handle missing matches and keep blanks tidy

If an ID does not occur in the product table, exact-match VLOOKUP normally returns #N/A. That error can be helpful while checking data, but a user-facing report may be clearer if it displays a short message. Wrap the lookup in IFNA to handle only the not-found error while allowing other problems to remain visible.

With IDs in A2:A and the same Products!A2:C table, enter this in B2 to show “Not found” for unmatched IDs while leaving empty input rows blank: =ARRAYFORMULA(IF(A2:A="", "", IFNA(VLOOKUP(A2:A, Products!A2:C, 3, FALSE), "Not found")))

The blank check comes first so unused rows do not fill with “Not found.” This can be important with open-ended references: without that check, every empty cell in the lookup range may be treated as a lookup request. Choose a message that makes sense for the sheet, or use an empty string if missing values should appear blank. Keep in mind that hiding an error also makes it less obvious that an ID needs correction.

Common problems and how to diagnose them

A formula can be syntactically valid yet fail to return the expected result. Check the data and range before changing the formula at random:

#N/A for an apparently present ID: Look for leading or trailing spaces, different punctuation, or values stored in different formats. A number and a text string that look alike may not match. Compare the source and lookup values directly, and make their formats and contents consistent.
#REF! or an expansion error: One or more cells in the output area may contain text, a formula, or another value. Clear the cells that need to receive the results. Also make sure the formula is not placed inside a range that it is supposed to fill.
Wrong return column: Recount the index starting at the first column of the VLOOKUP table range. Do not count from column A unless the range itself starts in column A.
Unexpected match: Confirm that the last VLOOKUP argument is FALSE. Also check for duplicate IDs; VLOOKUP returns the first matching row it finds, so duplicates can make the returned value seem arbitrary.
Formula only returns one result: Check that the lookup argument is a range, such as A2:A, rather than a single cell such as A2. Confirm that the formula is wrapped in ARRAYFORMULA and that the output cells are clear.

When debugging, try the lookup for one known ID in a spare cell first. If a single-value VLOOKUP cannot find that ID, the issue is probably the key, table range, match setting, or data—not the array behavior.

Duplicates, sorting, and lookup-table design

Exact-match VLOOKUP does not require the lookup table to be sorted. Sorting can still help people inspect a table, but it is not a requirement when the final argument is FALSE. Approximate matching is different and should not be substituted casually: it is intended for ordered ranges and can return a nearby value rather than an exact key.

Duplicates deserve particular attention. If the same product ID appears more than once in the lookup column, VLOOKUP returns the value from the first matching row. It does not report every matching row. If duplicate keys are invalid in your workflow, remove them or use a separate check to find them before relying on the lookup output.

Keep the key column at the left of the selected table range. VLOOKUP cannot search a column to the right and return a value from a column to its left. If the source layout cannot be changed, use a different lookup approach or construct a suitable range. Also be cautious when inserting or rearranging columns: a numeric column index can continue pointing to a valid column but no longer to the intended field.

ARRAYFORMULA VLOOKUP compared with alternatives

The combination is a good fit when you want a simple, automatically expanding lookup from a key column to a field on its right. It is not the only option, and the best choice depends on the data and the result you need.

VLOOKUP copied down: Use a regular VLOOKUP in each row if you want each result formula to be independently editable, or if an array output is awkward in the surrounding layout. The trade-off is maintaining formulas as rows are added.
XLOOKUP: Google Sheets also offers XLOOKUP, which can look in one range and return values from a separate range, including a range to the left. It can be easier to read when the lookup and result columns are not part of one contiguous table. Check availability for collaborators using older spreadsheet tools before choosing a newer function.
INDEX and MATCH: This combination can separate the search column from the return column and is useful for layouts VLOOKUP cannot handle directly. It is more involved to write than a straightforward VLOOKUP.
FILTER: Use FILTER when you want all rows that meet a condition, rather than only the first matching row. That distinction matters when keys are duplicated and every matching record should be shown.

Excel users should note the application difference: Google Sheets has ARRAYFORMULA, while Excel does not. Excel for Microsoft 365 uses dynamic-array behavior for many formulas and has its own lookup options, but a Sheets formula containing ARRAYFORMULA is not a drop-in Excel formula. If a workbook will move between the programs, confirm that the formulas are supported in both.

Keeping the sheet maintainable

A single array formula reduces repeated formulas, but it also creates a dependency: all results come from one cell. Label the output column clearly, leave its spill area open, and avoid placing notes or manual overrides in cells where results may expand. If a particular row needs a different value, change the source data or create a separate override column rather than typing over the array output.

Use a fixed table range when performance or a controlled data boundary matters, and an open-ended range when the lookup table is expected to grow. If the table is large, avoid making many separate formulas scan entire columns unnecessarily. Periodically verify that the return-column index still points to the correct field and that lookup keys remain unique where uniqueness is expected.

Frequently Asked Questions

Do I need to press Ctrl+Shift+Enter for ARRAYFORMULA in Google Sheets?
No. Enter the formula normally in the first result cell. Google Sheets expands the array result into the cells below when there is room.

Why does ARRAYFORMULA VLOOKUP return #N/A?
Usually the key is not an exact match in the first column of the lookup range. Check for missing records, spaces, inconsistent number-versus-text values, and the correct table range. Use IFNA if you want to display a chosen message for unmatched keys.

Can VLOOKUP return a value from a column to the left?
No. VLOOKUP searches the leftmost column of its table range and returns a column to its right. Use XLOOKUP or INDEX and MATCH when the return range is to the left of the lookup range.

Does the lookup table have to be sorted?
Not for an exact match when the last argument is FALSE. Approximate matching has different sorting requirements, so use it only when that behavior is intended.

What happens when there are duplicate lookup keys?
VLOOKUP returns the first matching row, not every match. Use FILTER if you need all matching rows, or address duplicate keys in the source table if each key is supposed to identify one record.

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.