In this tutorial, you will learn how to vlookup to return all matches in Google Sheets.
How to VLOOKUP to Return All Matches in Google Sheets
Quick Navigation
The VLOOKUP function is a powerful tool in Google Sheets that can be used to search for specific data in a given dataset. However, you are limited to a single match for each formula.
If your workflow requires you to return all matches for a given string, a better alternative is to use the FILTER function instead.
In this guide, we will show you how to use the FILTER function to return all matches in Google Sheets.
How to Return All Matches with FILTER in Google Sheets
Here’s our step-by-step guide on how to return all matches with vlookup in Google Sheets.
Step 1
First, select an empty cell where we’ll place the FILTER function.
In this example, we want to output the last names of all residents in the registry who live in a given state typed in cell I2.
Step 2
We’ll start by typing the “=FILTER(“ function into the formula bar.
Step 3
Next, we’ll add the range with the values we want to return.
In this example, our values are found in column B.
Step 4
Next, select the range we’ll use to compare with the criteria in cell I2.
In our example, we used the expression E2:E26 = I2 to ensure that the FILTER function returns just the last names where the state matches the given search query.
Step 5
Hit the Enter key to evaluate the function.
Changing the value in cell I2 will automatically update the results.
Summary
This guide should be everything you need to learn return all matches of a given lookup value in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.