How to VLOOKUP and Return Multiple Matches in Excel
A customer has several orders. A project has several owners. An invoice has several lines. VLOOKUP answers "what is one of them?" and stops — but the question is usually "what are all of them?"
Last updated: October 2026
| Customer | Orders | Match count | |
|---|---|---|---|
| Ana | → | A-1, A-2, A-3 | 3 |
| Ben | → | B-1 | 1 |
| Cara | → | 0 |
Method 1 — a TEXTJOIN array formula
- In Microsoft 365:
=TEXTJOIN(", ",TRUE,IF($A$2:$A$99=E2,$B$2:$B$99,"")). - In older versions it has to be confirmed with Ctrl+Shift+Enter, and TEXTJOIN may not exist at all.
- It returns an empty cell both when nothing matched and when the matches themselves were blank — two very different situations that look identical.
- Add a second column to bring back and the formula has to be written again, with the ranges adjusted by hand.
Method 2 — the 1-click way (Tellsheet)
Lookup All Matches
A wizard picks the two tables and matches them on a column by name — no A1 addresses, no column indexes. Lookup All Matches then brings back every matching value, joined into one cell, plus a match count so a joined cell is never opaque and "nothing matched" is visibly different from "matched a blank". Choose the separator, collapse repeated values or keep them, and bring back several columns at once. Your table stays exactly as tall as it was.
One cell, or one row per match?
Joining into one cell keeps your table's shape, which is what makes the result safe to drop in beside existing data — every formula next to it still points where it did. If you genuinely need a row per item, that is a reshape rather than a lookup: see Expand Rows by Quantity.
Frequently asked questions
Why does VLOOKUP stop at the first match?
By design — so do XLOOKUP and INDEX/MATCH. One-to-many needs another approach.
How do I join all matches in one cell?
A TEXTJOIN array formula, or Lookup All Matches from the wizard.
Does my table get taller?
No — one cell per row, so nothing beside it shifts.
Can I bring back more than one column?
Yes, each as its own joined column.
Related Excel guides
Every match, not just the first
Lookup All Matches joins all of them into one cell and counts them — with a preview before anything is written.
Get Tellsheet free See pricing