VLOOKUP Return Multiple Values in Google Sheets

VLOOKUP returns one value: the first match it finds. When your data has multiple rows that match the search key, VLOOKUP ignores everything after the first one. There is no built-in way to make VLOOKUP return all matches.

This guide covers three ways to return multiple values for a single lookup: the FILTER function (recommended), an INDEX MATCH array approach, and a helper column workaround.

In This Guide

• Why VLOOKUP Only Returns One Value
• Method 1: FILTER Function (Recommended)
• Method 2: INDEX MATCH with SMALL and IF
• Method 3: Helper Column with COUNTIF
• Which Method Should You Use?
• Tips and Best Practices
• Related Google Sheets Tutorials
• Frequently Asked Questions

Why VLOOKUP Only Returns One Value

VLOOKUP scans down the first column of a range and stops at the first row where the value matches. It has no concept of “keep searching” after finding a match.

Consider the sales records below. “Sherlock Holmes” appears in rows 2, 5, 9, and 13. A VLOOKUP for “Sherlock Holmes” returns only the data from row 2:

=VLOOKUP("Sherlock Holmes", B2:G10, 6, FALSE)

This returns $24.00, the commission from Fred’s first sale. His other three sales are ignored.

Method 1: FILTER Function (Recommended)

The FILTER function is the cleanest way to return multiple matching rows. It was designed for this exact use case.

Step-by-Step

In the sales records, Sherlock Holmes appears in multiple rows. A standard VLOOKUP returns only the first.

Enter the following formula:

=FILTER(A2:G10, B2:B10="Sherlock Holmes")

A2:G10 is the data range to return. B2:B10="Sherlock Holmes" is the condition. FILTER returns every row where column B equals “Sherlock Holmes.”

The formula returns all matching rows. Each result spills into the cells below and to the right automatically. No additional formulas needed.

Return specific columns with FILTER

If you only need the Product and Revenue columns for Sherlock Holmes:

=FILTER({D2:D10, F2:F10}, B2:B10="Sherlock Holmes")

The curly braces {} create an array of the specific columns you want. FILTER then returns only those columns for matching rows.

FILTER with a cell reference

Replace the hardcoded name with a cell reference:

=FILTER(A2:G10, B2:B10=I2)

Now changing cell I2 to any salesperson name returns all their records.

Method 2: INDEX MATCH with SMALL and IF

Before FILTER existed, the standard approach combined INDEX, MATCH, SMALL, IF, and ROW in an array formula. This method works but is harder to read and maintain.

=IFERROR(INDEX($D$2:$D$10, SMALL(IF($B$2:$B$10="Sherlock Holmes", ROW($B$2:$B$10)-ROW($B$2)+1), ROW()-ROW($J$1)), 1), "")

This formula goes in cell J2 and is copied down. Each row returns the next matching value using SMALL to iterate through the match positions.

How it works:

1. IF($B$2:$B$10="Sherlock Holmes", ROW(...)) builds an array of row positions where the name matches
2. SMALL(..., ROW()-ROW($J$1)) picks the 1st, 2nd, 3rd, etc. match position as the formula is copied down
3. INDEX($D$2:$D$10, ...) returns the value at that position
4. IFERROR(..., "") returns blank when there are no more matches

This approach is verbose but useful when you need compatibility with older spreadsheet versions.

Method 3: Helper Column with COUNTIF

A helper column approach uses COUNTIF to number each occurrence of a value, then VLOOKUP uses that number to identify which match to return.

Setup

In column H, add a running count formula:

=B2&"-"&COUNTIF($B$2:B2, B2)

This creates unique keys like “Sherlock Holmes-1”, “Sherlock Holmes-2”, “Sherlock Holmes-3” for each occurrence. Then use VLOOKUP to look up each numbered occurrence:

=VLOOKUP("Sherlock Holmes-2", H2:I10, 2, FALSE)

This returns the value from Sherlock Holmes’s second sale.

Which Method Should You Use?

For Google Sheets, FILTER is the right choice in nearly every case.

Tips and Best Practices

1. Use FILTER as the default for multiple-match lookups. It is cleaner, faster, and easier to modify than any VLOOKUP workaround.

2. Leave enough empty rows below FILTER results. If FILTER returns 5 rows but the cell below already has data, you get a #REF! error. Place FILTER results in an area with room to expand.

3. Combine FILTER with SORT. =SORT(FILTER(A2:G10, B2:B10="Sherlock Holmes"), 6, FALSE) returns all of Fred’s sales sorted by Revenue descending.

4. Use COUNTA or ROWS to count matches. =ROWS(FILTER(B2:B10, B2:B10="Sherlock Holmes")) tells you how many rows match before returning all of them.

5. Wrap FILTER in IFERROR for no-match scenarios. =IFERROR(FILTER(A2:G10, B2:B10="Nobody"), "No results") avoids the #N/A error when no rows match.

Related Google Sheets Tutorials

• VLOOKUP in Google Sheets - Complete guide covering syntax and all features
• FILTER Function - Full guide to FILTER for returning multiple rows
• VLOOKUP with Multiple Criteria - Match on two or more conditions
• INDEX MATCH in Google Sheets - Flexible alternative to VLOOKUP
• VLOOKUP for Beginners - Start here if you are new to VLOOKUP

Frequently Asked Questions

Can VLOOKUP return multiple values in Google Sheets?

Not by itself. VLOOKUP always returns the first match. To return all matching rows, use the FILTER function, which is designed for this purpose and requires no workarounds.

Why does VLOOKUP only return the first match?

VLOOKUP scans the first column from top to bottom and stops at the first match. It is designed for one-to-one lookups. For one-to-many lookups, FILTER is the right tool.

How do I use FILTER instead of VLOOKUP to return multiple results?

Use =FILTER(A2:D10, A2:A10="search_value"). FILTER returns every row where the condition is true, not just the first match. It spills results into multiple cells automatically.

Can INDEX MATCH return multiple values?

Not directly. INDEX MATCH also returns a single value. You can build array formulas with SMALL and IF to return multiple results, but FILTER is cleaner for this use case in Google Sheets.

What is the best way to find all matching rows in Google Sheets?

Use the FILTER function. It is built specifically for returning multiple rows that match a condition, it spills automatically, and the syntax is cleaner than any VLOOKUP workaround.