Look Up Many Values at Once (Spill)

Excel Formulas › Lookup

365 / 2021XLOOKUP

Give XLOOKUP a whole column of lookup values and it returns a whole column of results — one formula instead of one per row. The result spills automatically and stays in sync.

Quick formula: look up every ID in E2:E100 at once:
=XLOOKUP(E2:E100, ids, names)
Passing an array as the lookup value makes XLOOKUP spill a result for each one — no fill-down required.

Functions used (tap for the full reference guide):

XLOOKUP

The example

A column of IDs resolved to names in one formula.

AB
1IDName
2101Bo
3102Ann

The formula

One formula, many results:

=XLOOKUP(E2:E100, ids, names) // spills a result per lookup value

How it works

An array lookup value spills array results:

  1. Pass a range (not a single cell) as the lookup value: E2:E100.
  2. XLOOKUP returns a result for each value, spilling down the column.
  3. It stays linked to the input — add an ID and the result list grows (if the input spills too).
  4. Works with INDEX/MATCH in 365 as well: =INDEX(names, MATCH(E2:E100, ids, 0)) spills similarly.

One formula beats a thousand. A single spilling XLOOKUP is lighter and tidier than copying a formula down 1,000 rows — and there’s only one place to edit. Combine with FILTER or UNIQUE inputs to look up a dynamic set of keys.

Try it: interactive demo

Live demo

IDs (one per line) → names, all at once.

Variations

INDEX/MATCH spill

365 array MATCH:

=INDEX(names, MATCH(E2:E100, ids, 0))

With default

Per-row not-found:

=XLOOKUP(E2:E100, ids, names, "n/a")

Look up unique keys

Resolve a distinct set:

=XLOOKUP(UNIQUE(keys), ids, names)

Pitfalls & errors

Spill space. The results spill below the formula — keep that column clear or get #SPILL!.

Don’t also fill down. One spilling formula covers all rows; copying it down on top of the spill causes conflicts.

365/2021 only. Array-input lookups need dynamic arrays.

Practice workbook

📊
Download the free Look Up Many Values at Once (Spill) practice workbook
A spilling XLOOKUP over a column of keys (formula text + result) with INDEX/MATCH and default variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I look up a whole column of values at once in Excel?
Pass the range as the lookup value: =XLOOKUP(E2:E100, ids, names) spills one result per input — no fill-down needed. Requires Excel 365/2021.
Can INDEX/MATCH spill too?
Yes, in 365: =INDEX(names, MATCH(E2:E100, ids, 0)) returns an array of results.
Why do I get #SPILL!?
The result needs empty cells below to spill into. Clear the column or move the formula.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: XLOOKUP multi-column return · Spill range reference · Filter data

Function references: XLOOKUP