VLOOKUP with Multiple Matches in Excel

Today I will be showing how to use VLOOKUP to extract multiple matches in Excel.

One of the most amazing and widely used functions of Excel is VLOOKUP. So far we have learned to pull out a single match from a range of cells using the VLOOKUP function.

Read more: VLOOKUP with Multiple Criteria in Excel

Today I will be showing how you can pull out multiple matches from a range of cells using VLOOKUP.

Download Practice Workbook

VLOOKUP with Multiple Matches in Excel

Here we’ve got a data set with the Book Type, Book Name, and Author of some books in a bookshop called Martin Bookstore.

Today our objective is to pull out all the matches of each type of book using the VLOOKUP function.

We will execute this in two sections:

Section 1: Creating a Unique Name for Each Lookup Value

1. Insert a new column left to the lookup column and enter this formula in the first cell of it.

2. Then double click on or drag the Fill Handle to copy this formula to the rest of the cells.

You will find all the lookup values provided with a unique name, like Novel 1, Novel 2…, Poetry1, Poetry2…, etc.

Explanation of the Formula

• COUNTIF(C5:$C$24,C5) returns the total number of cells in the range C5:C24 (Book Type) that contain the value in cell C5 (Novel). See the COUNTIF function for details.

In simple words, how many novels there are. It is 7.

• C5&COUNTIF(C5:$C$24,C5) concatenates the value in cell C5 (Novel) with it. So it returns Novel7.
• When dragging the Fill Handle, C5 increments one by one, like C5, C6, C7… but C24 remains constant.

Therefore, for each Book Type, the earlier ones get excluded and a new name is generated.

For example, in the case of novels, Novel1 to Novel7 are generated, and similar for Poetry and other book types.

Section 2: Using the VLOOKUP Function

1. Create a new column with the Column Header as the lookup value.

2. Then insert this formula in the first cell of the column:

(Array Formula. So don’t forget to press Ctrl + Shift + Enter unless you are in Office 365)

You will find all the books of type Novel have been returned in the column.

Explanation of the Formula

• COUNTIF($C$4:$C$24,G3) tells how many cells in the range C4:C24 (Book Type) contain the value in cell G3 (Novel).

In simple words, how many novels there are in total. It is 7. See the COUNTIF function for details.

We have used the absolute cell reference of the range C4:C24 ($C$4:$C$24) because we want it to remain unchanged if we copy the formula to any cell.

• INDIRECT("A"&COUNTIF($C$4:$C$24,G3)) becomes INDIRECT("A"&7) and returns the cell reference A7. See the INDIRECT function for details.
• ROW($A$1:INDIRECT("A"&COUNTIF($C$4:$C$24,G3))) now becomes ROW(A1:A7).See the ROW function for details.

It returns an array from 1 to 7, {1, 2, 3, 4, 5, 6, 7}.

We used $A$1 because we do not want it to change if we copy the formula to another cell.

• G3&ROW($A$1:INDIRECT("A"&COUNTIF($C$4:$C$24,G3))) now concatenates the value in cell G3 (Novel) with the array returned by the ROW function and returns another array.

So it returns {Novel1, Novel2, …, Novel7}.

• Finally, VLOOKUP(G3&ROW($A$1:INDIRECT("A"&COUNTIF($C$4:$C$24,G3))),$B$4:$E$24,3,FALSE) becomes VLOOKUP({Novel1, Novel2, ..., Novel7},$B$4:$E$24,3,FALSE).

It searches for each value of the array {Novel1, Novel2, …, Novel7} in the lookup column B.

Then returns the corresponding Name of the novel from the 3rd column (Column_Index_Number is 3.)

Thus we get a list of all the novels.

Now if you want to get the Authors of the novels, change the Column_Index_Number to 4.

And for the other Book Types, insert their names side by side as Column Headers and drag the Fill Handle.

Multiple Matches in Excel in Alternative Ways

If the previous method seems hazardous, don’t worry. There are more options available.

1. Using FILTER Function

This is the most simple method indeed. Enter the Book Type as the Column Header and enter this FILTER formula in the first cell.

Image 8.

See, we have got a list of all the novels.

• Here $C$4:$C$24 (Book Name) is the lookup array. We are looking for the names of the books. You use your one.
• $B$4:$B$24 (Book Type) is the matching array. We want to match the book types. You use your one accordingly.
• F3 (Novel) is the matching value. We want to match the novels. You use accordingly.

Now, if you want the Book Names of all the Book Types, insert their names as the Column Headers side by side, and then drag the Fill Handle.

2. Using a Combination of IFERROR, INDEX, SMALL, and ROW and ROWS Functions (For Older Versions of Excel)

The FILTER function is only available in Office 365.

So if you use the older version of Excel, don’t worry. Insert the Book Type as the Column Header and enter this formula in the first cell:

(Array Formula. So don’t forget to press Ctrl + Shift + Enter unless you are in Office 365.)

Explanation of the Formula

• ROW(B4:B24) returns an array of {4, 5, 6, 7, …, 24}. And ROWS(B1:B3) returns 3. So ROW(B4:B24)-ROWS(B1:B3) returns an array of {1, 2, 3, …, 21}. See the ROW and ROWS function for details.
• IF(B4:B24=F3,ROW(B4:B24)-ROWS(B1:B3),"") returns the corresponding number from the array {1, 2, 3, …, 21} the value in cell F3 (Novel) matches the value in any cell of the range B4:B24 (Book Type). Otherwise returns a blank cell. See the IF function for details.

• SMALL(IF(B4:B24=F3,ROW(B4:B24)-ROWS(B1:B3),""),(ROW(B4:B24)-ROWS(B1:B3))) becomes SMALL({1, …, 3, …, 6, …, 20, …},{1, 2, 3, 4, ...., 21}) and returns the numbers first, then #NUM! errors the blank cells. See the SMALL function for details.

• INDEX($C$4:$C$24,(SMALL(IF(B4:B24=F3,ROW(B4:B24)-ROWS(B1:B3),""),(ROW(B4:B24)-ROWS(B1:B3))))) becomes INDEX($C$4:$C$24,{1,3,6,11,...,#NUM!}) and returns the corresponding Book Names (Name of the Novels) and #NUM! Error. See the INDEX function for details.

• Finally, we wrapped the formula inside an IFERROR function to turn the errors into blank cells.

Now, if you want, insert the other Book Types as Column Headers and drag the Fill Handle. You will get the books of the other book types.

Conclusion

Using these methods you can look for multiple matches with some values in any data set in Excel. Do you have any questions? Feel free to ask us.

Further Readings

• VLOOKUP and Return All Matches in Excel (7 Ways)
• VLOOKUP Partial Match Multiple Values (3 Approaches)
• How to Use VLOOKUP for Multiple Columns in Excel
• 10 Best Practices with VLOOKUP in Excel
• VLOOKUP to Return Multiple Values Horizontally in Excel
• VLOOKUP Max of Multiple Values (With Alternative)