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:

1. Organize your data in a table format, with headers in the first row.
2. 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.
3. 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))), "")

4. 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.
5. Drag this formula across the columns to the right to return all the matches for that unique value.
6. Drag this formula down to cover all the unique values in your list.
7. 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.

1. Create a unique list of the fruits in column D:

  D
1 Unique Fruits
2 Apple
3 Orange
4 Banana

2. 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))), "")

3. Press Ctrl+Shift+Enter to enter this formula as an array formula.
4. 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.