Today’s guide will show **how to return and display multiple matches using the VLOOKUP function** or apply the FILTER function.

**VLOOKUP returns a single result by default**. So, to display multiple results, you must apply some workarounds using **custom formulas**. We will show you various methods to perform the task.

## How do I get VLOOKUP to return multiple matches?

- Apply the
**FILTER function** **Use the array argument**that contains the possible matches**Set the include argument**to lookup_array = lookup_value- The formula returns
**multiple matches**

Here is the formula to get all matches using the **FILTER function**:

`=FILTER(C3:C7, B3:B7=”apple”)`

The first argument **[array]** contains the lookup value “apple”. The second argument, **[include]**, will find and extract multiple matches from the Sales column.

## How to use VLOOKUP with multiple results

If you want to use VLOOKUP for multiple matches, it is necessary to apply a little trick.

Because the FILTER function is available in Excel 2019 and above, we will show you the solution if you have an older Excel version.

First, we will **insert an additional column** (helper column) into the data table. With the help of the **COUNTIF function**, it’s easy to create a unique identifier for all matching results. The helper column uses the COUNTIF function to **create a unique ID for each instance**.

Take a closer look at the formula in **cell F2**:

`=B3&COUNTIF(B3:B$7,B3)`

To create a unique name for all instances, we need to merge the original lookup value using an ampersand character. This is why we leave the **B3** part of the expression unlocked. Copy the formula down!

In column B, we have multiple matches in the case of “apple”. Using the **COUNTIF** function, the formula will create a list in column F that contains unique names. **VLOOKUP** function will use the unique records as the first argument (lookup value) of the VLOOKUP function.

Let us see the next step!

Replace the original range with values that do not contain duplicate values. The new data set is located in range B3:B7. To get multiple matches using VLOOKUP, apply the following formulas to get the sales where the product name is “apple”:

```
=VLOOKUP(E3, B3:C7,2,0) = $1721
=VLOOKUP(E4, B3:C7,2,0) = $2057
```

## Get Multiple Matches using INDEX and MATCH

The following workaround uses the INDEX, MATCH, and INDIRECT functions to return all matches based on a lookup value.

**Note**: if you are working with array formulas, you must simultaneously press the **Ctrl + Shift + Enter** keys, then release all keys. You will see curly brackets around the formula.

**Formula:**

`={INDEX($C$3:$C$7, SMALL(IF(ISNUMBER(MATCH($B$3:$B$7,$E$3,0)), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)),""), ROWS($A$1:A1)))}`

**Explanation**

The **MATCH** function returns an array. In case of a match, the function returns with 1. Else, we get #N/A.

```
=MATCH($B$3:$B$7,$E$3,0)
={1,#N/A,1, #N/A, #N/A }
```

We use the **ISNUMBER** function to convert the numeric values to **boolean values**:

```
=ISNUMBER(MATCH($B$3:$B$7,$E$3,0))
={TRUE, FALSE, TRUE, FALSE, FALSE}
```

The next step is to convert boolean values to row numbers and blanks using the **IF** function. For example, use the **MATCH** and **ROW** functions to create a sequential order from 1 to n. The formula returns an array that **contains the positions of the matching values** in column D.

`=IF(ISNUMBER(MATCH($B$3:$B$7, $E$3, 0)), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")`

**Result:**

`={1, "" ,3, "", ""}`

To generate the n-th smallest row number, use the **SMALL** function.

`=SMALL(IF(ISNUMBER(MATCH($B$3:$B$8, $E$3, 0)), MATCH(ROW($B$3:$B$8), ROW($B$3:$B$8)), ""), ROWS($A$1:A1))`

The last part of the formula uses the ROWS function and returns the following result:

`=SMALL({1;"";3;"";"";}, 1)`

Finally, the **INDEX** function will return the matching records based on the row and column numbers:

As a result of the formula, Excel displays all corresponding values if the lookup value is “apple”.