INDEX MATCH with ARRAYFORMULA in Google Sheets

INDEX MATCH normally looks up one value at a time. It is tempting to wrap it in ARRAYFORMULA to fill a whole column from one cell, but that does not work, because INDEX only ever returns the first match. This guide shows exactly why the ARRAYFORMULA approach fails, gives you the MAP + LAMBDA formula that actually returns a result for every row, and covers blank rows, open-ended ranges, and common errors.

In This Guide

• Why One Formula for a Whole Column?
• The Formula Pattern
• Step-by-Step Setup
• Handle Blank Rows
• Open-Ended Ranges for Growing Data
• Common Errors
• Tips
• Related Google Sheets Tutorials
• Frequently Asked Questions

Why One Formula for a Whole Column?

When you have a column of lookup values (like a list of Product IDs), the standard approach is to write an INDEX MATCH formula in the first row and copy it down. This works, but it creates a separate formula in every cell.

Problems with copying formulas down:

• Hundreds of individual formulas slow down recalculation
• New rows require manually copying the formula to each one
• Editing the formula means updating every cell, not just one

The goal is one formula, in one cell, that fills every row automatically. The catch is that the obvious tool for this, ARRAYFORMULA, does not work with INDEX MATCH. The next section explains why, and what to use instead.

The Formula Pattern

The natural instinct is to swap the single cell H2 for the range H2:H5 and wrap the whole thing in ARRAYFORMULA:

This does not return four different prices. It returns the first price, repeated four times (for example $35.00 in all four rows). MATCH correctly produces an array of four positions, but INDEX is not array-aware over its position argument, it reads only the first position and ignores the rest. ARRAYFORMULA cannot fix this, because the limitation lives inside INDEX itself.

The working pattern uses MAP with LAMBDA to run a separate INDEX MATCH for each value in the search column:

MAP walks down every value in search_column, and the LAMBDA runs a fresh INDEX(..., MATCH(id, ...)) for that single value. Because each row gets its own INDEX call, each row gets its own correct result. The IF(id="", "", ...) check handles blank rows so they return an empty string instead of an error.

Step-by-Step Setup

We’ll look up prices from the product inventory for a list of Product IDs.

Sample Data

The product inventory has Product IDs in column A and Prices in column D (8 products, rows 2 through 9). A separate list of Product IDs sits in column H (4 IDs in H2:H5).

The inventory table runs from A1:F9. The lookup list in H2:H5 contains four Product IDs: SKU-103, SKU-106, SKU-101, and SKU-108. You want to return the price for each one in column I.

Start with a standard INDEX MATCH formula in I2:

This returns $35.00, the price for SKU-103. But it only works for one row. Copying it down to I3, I4, and I5 works, but leaves you with four separate formulas to maintain.

You might expect to replace H2 with the range H2:H5 and wrap it in ARRAYFORMULA. That returns $35.00 four times, not four different prices, INDEX only reads the first match. Use MAP with LAMBDA instead:

Enter this formula in I2 only. MAP runs the INDEX MATCH once per Product ID, returning all four prices at once:

One formula, four results. Cells I3:I5 show results but contain no formula of their own, they are filled by the MAP in I2.

Handle Blank Rows

Without a blank check, an empty cell in the search column makes MATCH look for an empty string, which produces a #N/A error for that row.

The IF(id="", "", ...) check inside the LAMBDA suppresses this:

When H3 is empty, the formula returns an empty string for that row instead of an error. All other rows still return their lookup results.

Open-Ended Ranges for Growing Data

If new Product IDs get added to column H regularly, pass an open-ended range to MAP:

H2:H (no end row) means “H2 to the bottom of the sheet.” New IDs added in H6, H7, or beyond are included automatically. The IF(id="", "", ...) check is essential here, an open-ended range includes thousands of empty rows, and without it every one would return an error.

Common Errors

Same result repeated in every row

If every row shows the same value, for example $35.00 four times instead of four different prices, you are almost certainly using ARRAYFORMULA(INDEX(..., MATCH(H2:H5, ...))). INDEX ignores the array of positions from MATCH and returns only the first one, and ARRAYFORMULA cannot change that.

Fix: Switch to MAP + LAMBDA so each row runs its own INDEX MATCH: =MAP(H2:H5, LAMBDA(id, INDEX(D2:D9, MATCH(id, A2:A9, 0)))).

#REF!: Cells below are not empty

A spilled array needs empty cells below the formula cell to fill. If I3 contains a value or formula, the MAP in I2 returns #REF!.

Fix: Clear all cells in the output column below the formula cell.

#N/A: Lookup value not found

A Product ID in column H does not exist in the inventory. MATCH cannot find it and returns #N/A.

Fix: Wrap the lookup in IFERROR: IFERROR(INDEX(D2:D9, MATCH(id, A2:A9, 0)), "Not found").

Slow performance

MAP over an open-ended range on large sheets (10,000+ rows) can be slow because it evaluates the LAMBDA for every row.

Fix: Use a bounded range (H2:H500) instead of H2:H. For very large datasets, an array-aware ARRAYFORMULA(VLOOKUP(H2:H, ...)) is often faster than MAP, see VLOOKUP with ARRAYFORMULA.

Tips

1. Reach for MAP, not ARRAYFORMULA, with INDEX MATCH. ARRAYFORMULA does not spill INDEX MATCH down a column, INDEX returns only the first match. MAP runs a fresh lookup per row and actually works.

2. Always include the blank check. IF(id="", "", ...) inside the LAMBDA prevents #N/A errors on empty rows. This is essential when using open-ended ranges like H2:H.

3. Use IFERROR for missing values. Not every lookup value may exist in the source data. IFERROR handles this gracefully without breaking the spill.

4. Need a true array-aware wrapper? Use VLOOKUP. Unlike INDEX MATCH, VLOOKUP accepts an array of search keys, so ARRAYFORMULA(VLOOKUP(H2:H5, ...)) spills correctly. Use MAP + LAMBDA when you need INDEX MATCH specifically, for example, left lookups where the return column is to the left of the lookup column. See VLOOKUP with ARRAYFORMULA.

5. For returning multiple matching rows, use FILTER. MAP with INDEX MATCH returns one result per search key. If a single search key has multiple matches, see INDEX MATCH to return multiple results.

Related Google Sheets Tutorials

• INDEX MATCH: The Complete Guide: Full syntax, left lookups, and the combined formula pattern
• INDEX MATCH for Beginners: Step-by-step tutorial for your first formula
• ARRAYFORMULA: Complete Guide: What ARRAYFORMULA can and cannot apply to an entire column
• VLOOKUP with ARRAYFORMULA: The array-aware wrapper that does spill down a column
• INDEX MATCH to Return Multiple Results: Return all matching rows, not just the first