How to Vlookup with Multiple Matches in Excel (with Easy Steps)

Formula Breakdown

• COUNTIF(C5:$C$25,C5) returns the total number of cells in the range C5:C25 (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$25,C5) concatenates the value in cell C5 (Novel) with it.
  • So it returns Novel7.

Dragging the Fill Handle tool, C5 increments one by one, like C5, C6, C7… but C25 remains constant. For each Book Type, the earlier ones get excluded and a new name is generated.

• Press ENTER.

• Place the cursor to the right-bottom corner of cell B5 to get the Fill Handle
• Double-click on it.

It copies the formula to the rest of the cells. You will find all the lookup values provided with a unique name, like Novel1, Novel2…, Poetry1, Poetry2…, etc.

Read More: VLOOKUP and Return All Matches in Excel (7 Ways)

---

Step 2 – Use VLOOKUP Function

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

• Insert the following formula in cell G5:

=VLOOKUP(G$4&ROW($A$1:INDIRECT("A"&COUNTIF($C$5:$C$25,G$4))),$B$5:$E$25,3,FALSE)

Formula Breakdown

• COUNTIF($C$5:$C$25,G$4) tells how many cells in the range C5:C25 (Book Type) contain the value in cell G4 (Novel).
  • In simple words, how many novels there are in total. It is 7.

We have used the absolute cell reference of the range C5:C25 ($C$5:$C$25) because we want it to remain unchanged if we copy the formula to any cell.

• INDIRECT(“A”&COUNTIF($C$5:$C$25,G$4)) becomes INDIRECT(“A”&7) and returns the cell reference A7. See the INDIRECT function for details.
• ROW($A$1:INDIRECT(“A”&COUNTIF($C$5:$C$25,G$4))) now becomes ROW(A1:A7).See the ROW function for details.
  • It returns an array from 1 to 7 like {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.

• G$4&ROW($A$1:INDIRECT(“A”&COUNTIF($C$5:$C$25,G$4))) now concatenates the value in cell G4 (Novel) with the array returned by the ROW function and returns another array.
  • So it returns {Novel1, Novel2, …, Novel7}.

• VLOOKUP(G$4&ROW($A$1:INDIRECT(“A”&COUNTIF($C$5:$C$25,G$4))),$B$5:$E$25,3,FALSE) becomes VLOOKUP({Novel1, Novel2, …, Novel7},$B$5:$E$25,3,FALSE).

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

Then it returns the corresponding name of the novel from the 3rd column (as the col_index_num is 3).

• Press ENTER.

Note: It’s an array formula. So don’t forget to press Ctrl + Shift + Enter unless you are in Excel 365.

And for the other Book Types,

• Insert their names side by side as Column Headers and drag the Fill Handle.

---

Similar Readings

• VLOOKUP Not Working (8 Reasons & Solutions)
• Excel LOOKUP vs VLOOKUP: With 3 Examples
• Why VLOOKUP Returns #N/A When Match Exists (with Solutions)
• Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
• Excel VLOOKUP to Find Last Value in Column (with Alternatives)

---

Alternative Ways to Vlookup with Multiple Matches in Excel

Method 1 – Using FILTER Function

• Insert the Book Type as the Column Header and enter the following formula in cell F5.

=FILTER($C$5:$C$25,$B$5:$B$25=F$4)

Formula Breakdown

Here,

• $C$5:$C$25 (Book Name) is the lookup_array. We are looking for the names of the books. You use your one.
• $B$5:$B$25 (Book Type) is the matching_array. We want to match the book types. You use your one accordingly.
• F4 (Novel) is the matching_value. We want to match the novels. You use it accordingly.

Formula Breakdown

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

• SMALL(IF($B$5:$B$25=F4,ROW(B5:B25)-ROWS(B1:B4),””),(ROW(B5:B25)-ROWS(B1:B4))) becomes SMALL({1, …, 3, …, 6, …, 20, …},{1, 2, 3, 4, …., 21}) and returns the numbers first, then #NUM! errors in the blank cells. See the SMALL function for details.

• INDEX($C$5:$C$25,(SMALL(IF($B$5:$B$25=F4,ROW(B5:B25)-ROWS(B1:B4),””),(ROW(B5:B25)-ROWS(B1:B4))))) becomes INDEX($C$5:$C$25,{1,3,6,11,…,#NUM!}) and returns the corresponding Book Names (Name of the Novels) and #NUM! errors. See the INDEX function for details.

• We have wrapped the formula inside an IFERROR function to turn the errors into blank cells.
• Press ENTER.

• Insert the other Book Types as Column Headers and drag the Fill Handle.

Read More: 7 Practical Examples of VLOOKUP Function in Excel

---

Method 3 – Vlookup with Multiple Matches and Return Results in a Row

• Go to cell G5 and enter the formula below.

=IFERROR(INDEX($D$5:$D$19,SMALL(IF($F5=$B$5:$B$19,ROW($D$5:$D$19)-4,""),COLUMN()-6)),"")