The VLOOKUP function only returns a single match. And what if a lookup value has multiple matches within a range? Let’s see the options you have.

## How to VLOOKUP for multiple matches

We imported a dataset from Google Sheets to Excel using Coupler.io, a solution for automatic data exports from multiple apps and sources. Now we need to lookup email addresses for a user with the name “Lynn“. The VLOOKUP formula only returns one match, whereas there are a few.

Read more about Microsoft Excel integrations for data export on a schedule.

The best way to lookup all the matches is the FILTER function, which is available for Excel Online and Excel for Microsoft 365.

Other Excel users will have to use a workaround to return all the matches, but it is still possible.

## Excel FILTER function

Syntax

``=FILTER(array,include,[if_empty])``
• `array` – the range or array to filter.
• `include` – filter criteria.
• `if_empty` – the optional parameter to return when no results meet the criteria.

In our example, the FILTER formula to return multiple values will look as follows:

`=FILTER(D2:D100,B2:B100=H5)`

If for any reason you can’t or don’t want to go with Excel Online or Excel 365, you’ll need to tinker with the following workaround.

## VLOOKUP for multiple results (workaround)

The logic of this workaround is the following:

• Step1: Create a separate column where we will create unique strings for our lookup value, such as Lynn1, Lynn2, Lynn3, etc. This will let us differentiate lookup values.
• Step2: Look up these unique strings to return matching values.

### Step1: Create a column with unique strings for the lookup value

Insert a column to the left of your dataset like this:

Insert the following COUNTIF formula in the A2 cell and drag it down:

`=C2&COUNTIF(\$C\$2:C2,C2)`

Note: Unfortunately, we can’t apply an array formula for this.

### Step2: VLOOKUP formula for multiple values

Now we can insert a VLOOKUP formula to return all the matching results for our lookup value:

`=VLOOKUP("Lynn"&ROW(A1:A100),\$A\$2:\$F\$100,5,FALSE)`
• `"Lynn"&ROW(A1:A100)` – a formula, which will generate a unique string to be used as a lookup value, such as Lynn1, Lynn2, Lynn3, etc.
• `\$A\$2:\$F\$100` – the range to look up
• `5` – the column to return the matching values from

Do not forget about the method of applying array formulas in Excel desktop:

• Select an array for the VLOOKUP formula
• Insert the formula to the formula bar and press Ctrl+Shift+Enter for Windows (Command+Return for Mac)

To get rid of `#N/A`, let’s nest our VLOOKUP formula with IFERROR as follows:

`=IFERROR(VLOOKUP("Lynn"&ROW(A1:A100),\$A\$2:\$F\$100,5,FALSE),"")`

Now it looks better:

Good luck with your data!

Back to Blog