How to How to VLOOKUP with Multiple Results in Excel in Excel - Practical Guide

Learn how to retrieve multiple matching results from a VLOOKUP instead of just the first match. This tutorial covers using FILTER, INDEX-MATCH arrays, and helper columns to return all occurrences of a lookup value, essential for analyzing duplicate entries, customer orders, or multi-match datasets.

Why This Matters

Standard VLOOKUP returns only the first match, limiting analysis of datasets with duplicates. Mastering multi-result lookups is critical for financial reporting, inventory management, and data consolidation tasks.

Prerequisites

•Basic understanding of VLOOKUP function syntax
•Knowledge of array formulas and helper columns
•Familiarity with Excel 365 FILTER function (or Excel 2019+ for alternatives)

Step-by-Step Instructions

Prepare your data structure

Organize your lookup table with the search column on the left. Ensure all data is in contiguous ranges without blank rows to avoid lookup errors.

Use FILTER function (Excel 365+)

Enter formula =FILTER(return_range, (lookup_range=criteria)) in a blank cell. Example: =FILTER(B:B, A:A="Smith") returns all B column values where column A contains "Smith".

Create INDEX-MATCH array formula (Excel 2019)

Use =IFERROR(INDEX(return_range, SMALL(IF(lookup_range=criteria, ROW(lookup_range)), ROW())), "") and press Ctrl+Shift+Enter to return multiple matches sequentially.

Add a helper column for row counting

In a new column, create a formula like =COUNTIF($A$1:A1, A1) that increments for duplicate values, allowing you to identify the 1st, 2nd, 3rd occurrence.

Test and validate results

Cross-check returned values against your source data using Data > Filter > AutoFilter to visually confirm all matches are captured without duplicates.

Alternative Methods

FILTER function (Excel 365)

Simplest modern solution that returns all matching rows in one formula without array entry or helper columns.

INDEX-SMALL-IF array formula

Works in Excel 2019 and earlier; requires Ctrl+Shift+Enter but provides flexibility without FILTER availability.

VLOOKUP with helper column approach

Uses COUNTIF to number duplicates, then combines with VLOOKUP to retrieve nth occurrence—more manual but universally compatible.

Tips & Tricks

✓Use FILTER in Excel 365 for the cleanest, most efficient multi-result lookup without helper columns.
✓When using array formulas, remember to press Ctrl+Shift+Enter, not just Enter, to activate array processing.
✓Add error handling with IFERROR to display blank cells instead of #N/A when no more matches exist.
✓Sort your lookup table alphabetically if possible to make visual verification of results easier.

Pro Tips

★Combine FILTER with SORT for automatic multi-result sorting: =SORT(FILTER(range, criteria), column, TRUE).
★Use TEXTJOIN with IF array formula to consolidate multiple results into a single cell: =TEXTJOIN(", ", TRUE, IF(criteria, return_range, "")).
★For large datasets, consider using Power Query (Data > Get & Transform > From Table) instead of formulas for better performance.

Troubleshooting

Formula returns #N/A or #VALUE! error

Verify the lookup value exists in the lookup range with exact spelling/formatting. Check array formula entry with Ctrl+Shift+Enter. Ensure data types match (text vs numbers).

FILTER returns only first result instead of all matches

Confirm you're using Excel 365; FILTER function isn't available in earlier versions. Use INDEX-MATCH array formula as alternative.

Helper column approach shows blanks or #REF! errors

Check that SMALL function references are correctly nested and ROW() function is applied to entire ranges, not single cells.

Performance is slow with large datasets

Replace array formulas with FILTER (Excel 365) or use Power Query to process data more efficiently without recalculation overhead.

Related Excel Formulas

FILTER INDEX MATCH SMALL IF TEXTJOIN COUNTIF IFERROR

Frequently Asked Questions

Can standard VLOOKUP return multiple results?

No, VLOOKUP only returns the first match. Use FILTER (Excel 365), INDEX-MATCH arrays, or helper columns to retrieve multiple results.

What's the difference between FILTER and INDEX-MATCH for multiple results?

FILTER is simpler and built for this purpose in Excel 365, returning results in a dynamic range. INDEX-MATCH requires array formulas and is more complex but works in older Excel versions.

How do I combine multiple VLOOKUP results into one cell?

Use TEXTJOIN with IF array formula: =TEXTJOIN(", ", TRUE, IF(lookup_range=criteria, return_range, "")). Press Ctrl+Shift+Enter to activate the array.

Will helper columns slow down my spreadsheet?

Helper columns have minimal performance impact on typical datasets but may slow very large files (100k+ rows). Use FILTER or Power Query for optimal performance on big data.