Although **VLOOKUP** is a very useful formula and can help us get the results we want with success and easier, certain shortcomings are worth mentioning.

One of those things is to retrieve **multiple results with VLOOKUP**. However, there are some workarounds to resolve even this issue.

## Using the FILTER function

For our example, we will use the simple table of sales data, with the names of salespersons that achieved those sales:

You will notice that some names, such as Brad, are found multiple times on our table. If we would go and use a **VLOOKUP to extract** only the data for Brad from our table, we would end up only with the sales results of the first instance of the name **(row number 8)**:

To get the result for the second Brad, we **cannot use VLOOKUP**, at least not with the current state of our table. To get the results for both persons named Brad, we can use the **FILTER formula**. This is a relatively new formula that is available in **Excel Online or Microsoft 365 version**.

It has **three parameters**, out of which **two are mandatory**, and the last one is **optional**. The first parameter is the **array**, and it represents the range that we want to filter. The second one is **include**, and it stores our filter criteria. The third one is the **if_empty parameter**, which we use to display a value of our choosing if the formula does not retrieve anything.

In our example, the array will be **column B (sales results)**, and include will search for all the values Brad in **column A**.

Having in mind that we will insert the word **Brad in cell D6**, this will be our formula:

1 |
=FILTER(B:B,A:A=D6) |

And the results will be displayed in sequential order, one after the other:

The fact that the cell borders are blue goes to show that this is a **built-in function** in Excel.

## VLOOKUP With Multiple Results

There is a workaround for using **VLOOKUP** to achieve the same results that we had with the **FILTER formula** above. To achieve this, we first need to insert the helper’s column, to give a unique identifier for every name in our table.

We will add a column in front of **column A**, and insert the following formula in **cell A2**:

1 |
=B2&COUNTIF($B$2:B2,B2) |

This formula will check if the value in **column B** exists already. If it does, a number will be added to it. When we drag this formula till the end of our table, this will be the result:

At this point, we can simply use the **unique identifier** to retrieve our results, but that would not help us a lot. Instead of that, we will change the **lookup_value**, and this will be a **lookup_value** for our **VLOOKUP formula**:

1 |
H3&ROW(A1:A11) |

In **cell H3**, we will put the name of the persons that we want to retrieve the results for. We concatenate this result with the **ROW formula**, and we will use the rows located in **column A**. Our table array will be **range A2:C11**. As a final result, this will be a formula that we will insert in **cell I3**:

1 |
=VLOOKUP(H3&ROW(A1:A11),$A$2:$C$11,3,FALSE) |

In the previous versions of Excel, you would need to press **CTRL + SHIFT + ENTER** before inserting the formula. In the **online version of Excel and Office 365**, we do not need to do this, as the formula will be laid out to us:

You will notice that we have a lot of **#N/A results**. To get rid of these, we will add **IFERROR** at the beginning of the formula, so it will be:

1 |
=IFERROR(VLOOKUP(H3&ROW(A1:A11),$A$2:$C$11,3,FALSE),"") |

And our results will look much better: