# How to Use VLOOKUP to Return All Matches in Excel

VLOOKUP is a powerful function in Excel that allows you to search for and return specific data from a table. However, by default, VLOOKUP only returns the first match it finds in the table. If you want to return all the matches, you'll need to use a combination of other functions like INDEX, SMALL, and IFERROR along with an array formula.

Here's a step-by-step guide on how to use VLOOKUP to return all matches in Excel:

- Organize your data in a table format, with headers in the first row.
- In a separate column, create a unique list of the values you want to look up. You can use Excel's "Remove Duplicates" feature to help with this.
- In the next column, enter the following formula (assuming you're looking up values in column A, and the unique list starts in cell D2):

`=IFERROR(INDEX($B$1:$B$100, SMALL(IF($A$1:$A$100=D2, ROW($A$1:$A$100)-MIN(ROW($A$1:$A$100))+1, ""), COLUMN(A1))), "")`

- Press Ctrl+Shift+Enter to enter this formula as an array formula. Excel will surround the formula with curly braces {} to indicate that it's an array formula. Do not type these braces manually.
- Drag this formula across the columns to the right to return all the matches for that unique value.
- Drag this formula down to cover all the unique values in your list.
- The results will show all the matches for each unique value in the list. If there are no more matches, the cells will display a blank value.

Here's an example:

## Example

Let's say you have the following data:

```
| A B
1 | Fruit Price
2 | Apple $1.00
3 | Orange $0.75
4 | Apple $1.20
5 | Banana $0.50
6 | Orange $0.80
7 | Apple $1.10
8 | Banana $0.55
```

You want to use VLOOKUP to return all the prices for each unique fruit.

- Create a unique list of the fruits in column D:

```
D
1 Unique Fruits
2 Apple
3 Orange
4 Banana
```

- In cell E2, enter the array formula mentioned above:

`=IFERROR(INDEX($B$1:$B$8, SMALL(IF($A$1:$A$8=D2, ROW($A$1:$A$8)-MIN(ROW($A$1:$A$8))+1, ""), COLUMN(A1))), "")`

- Press Ctrl+Shift+Enter to enter this formula as an array formula.
- Drag this formula across the columns to the right and down to cover all the unique fruits.

Your final result should look like this:

```
D E F G
1 Unique Fruits
2 Apple $1.00 $1.20 $1.10
3 Orange $0.75 $0.80
4 Banana $0.50 $0.55
```

As you can see, all the prices for each unique fruit have been returned using VLOOKUP along with other functions in an array formula.

Did you find this useful?