Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# How to VLOOKUP and Return Multiple Values Vertically in Excel

If you are looking for ways to VLOOKUP and return multiple values vertically in Excel, then you may find this article helpful. Here, we used different combinations of functions to extract multiple values vertically for a lookup value. So, let’s start with our main article.

## 3 Ways to VLOOKUP and Return Multiple Values Vertically VLOOKUP in Excel

Here we’ve got a data set with the Book Types, Book Names, and Authors of some books from a bookshop called Martin Bookstore. Today our objective is to lookup for a specific value and return all the values based on a specific match using different functions of Excel. For creating this article, we have used Microsoft Excel 365 version. However, you can use any other version at your convenience.

### Method-1: Applying a Combination of VLOOKUP and COUNTIF Functions to Return Multiple Values Vertically

Here, we look for the novel-type books and then return the name of these novels in the following indicated column. To do this we are going to use the VLOOKUP function here. Steps:

• Insert a new column named Helper before the dataset.
• Type the following formula in cell B5.

`=C5&COUNTIF(C5:\$C\$14,C5)`

Here C5 and C14 are respectively the first and last cells of the Book Type column. You can use your range. The COUNTIF function will count the times of presence of a book type.  This will generate a series of Book Types along with their serial numbers. Like Novel1, Novel2, etc… • Now, go to another new column and put the name of the Book Type that you want to look up as the Column Header. Here, we have used Novel as the Column Header. • Write down the following formula in cell G5.
`=VLOOKUP(G4&ROW(B1:INDIRECT("A"&COUNTIF(C5:C14,G4))),B5:E14,3,FALSE)`

Formula Breakdown

• `Here ``G4`` is the ``Column Header`` of the ``Book Type`` (``Novel``).`
• `C5:C14`` is the first column of my data set (``Book Type``).`
• `B4:E14`` is the whole data set, along with the extra column right in front of the ``Book Type `
• `3`` is the column number of the data that we want to extract ``(Book Name`` in this example) considering the extra (``Helper``) column.`
• `COUNTIF(C4:C13, G3) ``tells how many cells in the range ``C4:C13 (Book Type)`` contain the value in cell ``G3 (Novel)``.`
• `In simple words, how many novels there are in total? It is ``4``.`
• `INDIRECT("A"&COUNTIF(C4:C13,G3)) ``becomes ``INDIRECT("A"&4) ``and returns the cell reference ``A4``. See` the INDIRECT function `for details.`
• `ROW(A1:INDIRECT("A"&COUNTIF(C4:C13,G3))) ``now becomes ``ROW(A1:A4)``.`
• `It returns an array from 1 to 7, ``{1, 2, 3, 4}``. See` the ROW function `for details.`
• `G3&ROW(A1:INDIRECT("A"&COUNTIF(C4:C13,G3))) ``now concatenates the value in cell ``G3 (Novel)`` with the array returned by the ``ROW function``. So it returns another array ``{Novel1, Novel2, Novel3, Novel4}``.`
• `Finally, ``VLOOKUP(G3&ROW(A1:INDIRECT("A"&COUNTIF(C4:C13,G3))),B4:B13,3,FALSE) ``becomes ``VLOOKUP({Novel1, Novel2, ..., Novel7},B4:E13,3,FALSE).`
• `It searches for each value of the array ``{Novel1, Novel2, ..., Novel4} ``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.` • Press ENTER. As it is an Array Formula, don’t forget to select multiple cells earlier and press CTRL+SHIFT+ENTER if you are using other versions except for Microsoft 365.

As you can see, we have got all the books of the type “Novel”.
You can repeat the same procedure for Poetry, Autobiography, or Non-Fiction. Just change the Column Header from “Novel” to your desired one. Note: This method has one limitation. That is, it exhibits multiple matches in reverse order. As a result, It is displaying the last novel “The Forty Rules of Love” first, the first novel “A Tale of Two Cities” last, and so on.

Read More: INDIRECT VLOOKUP in Excel

### Method-2: Utilizing a Combination of INDEX, SMALL, and ROWS Functions

If you’re using the older version of Excel, can apply the VLOOKUP function described earlier in this article. But that method has one limitation. It exhibits the multiple names in reverse order.
To exhibit the multiple matches in the original order in the old versions of Excel, you can use a complex formula consisting of the IFERROR function, INDEX function, SMALL function, ROW function, and ROWS function of Excel. Steps:

• Insert the following formula in cell F5.
`=IFERROR(INDEX(B5:D14,SMALL(IF(B5:B14=F4,(ROW(B5:B14)-ROWS(B1:B4)),""),(ROW(B5:B14)-ROWS(B1:B4))),2),"")`

Formula Breakdown

• `B5:B14=F4 → ``checks the list of products from ``Column B ``with ``Book Type ``Novel``. For the ``Novel, ``we will have ``TRUE``.`
• `Output → {TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}`
• `ROW(B5:B14) → The ROW function ``will return the row number of this range.`
• `Output → {5;6;7;8;9;10;11;12;13;14}`
• `ROWS(B1:B4) → ``gives the total number of rows in this range`
• `Output → 4`
• `ROW(B5:B14)-ROWS(B1:B4) → ``becomes`
• `{5;6;7;8;9;10;11;12;13;14}-4 → {1;2;3;4;5;6;7;8;9;10}`
• `IF(B5:B14=F4,(ROW(B5:B14)-ROWS(B1:B4)),"") → ``becomes`
• `IF({TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10},"")`
• `Output → {1;"";"";4;5;"";"";8;"";""}`
• `SMALL(IF(B5:B14=F4,(ROW(B5:B14)-ROWS(B1:B4)),""),(ROW(B5:B14)-ROWS(B1:B4)) → ``becomes`
• `SMALL({1;"";"";4;5;"";"";8;"";""},{1;2;3;4;5;6;7;8;9;10})`
• `Output → {1;4;5;8;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}`
• `Finally, ``the INDEX function ``will return the book names in these positions.` • Press ENTER. As it is an Array Formula, don’t forget to select multiple cells earlier and press CTRL+SHIFT+ENTER if you are using other versions except for Microsoft 365.

Finally,  we have got all the books of the type “Novel”. ### Method-3: Using FILTER Function to VLOOKUP and Return Multiple Values Vertically

The method described above is useful, yet a bit complex.
If you use the new version of Excel (or Office 365), you can accomplish the same task using only a single function, the FILTER function. Steps:

• Put down the following formula in cell F5.
`=FILTER(\$C\$5:\$C\$14,\$B\$5:\$B\$14=\$F\$4)`
• Here \$C\$5:\$C\$14 is the column from which I want to extract data (Book Name).
• \$B\$5:\$B\$14 is the first column of my data set (Book Type).
• And \$F\$4 is the Column Header containing the type of book I want to extract (Novel). • Press ENTER.

See, we have got all the books of the type “Novel”.
You can repeat the same procedure for Poetry, Autobiography, or Non-Fiction. Just change the Column Header from “Novel” to your desired one. ## How to VLOOKUP and Return Multiple Values Horizontally in Excel

In this section, we will show the process of returning multiple values horizontally after matching up with the book-type Novel. Steps:

• Write down the following formula in cell B17.
`=INDEX(\$C\$5:\$C\$14, SMALL(IF(\$B\$16=\$B\$5:\$B\$14, ROW(\$B\$5:\$B\$14)-MIN(ROW(\$B\$5:\$B\$14))+1, ""), COLUMNS(\$A\$1:A1)))`

Formula Breakdown

• ROW(\$B\$5:\$B\$14) → returns the row number in array form: {5;6;7;8;9;10;11;12;13;14}
• SMALL(IF(\$B\$16=\$B\$5:\$B\$14,ROW(\$B\$5:\$B\$14)-MIN(ROW(\$B\$5:\$B\$14))+1, “”) → gives the first small number within the array.
• INDEX(\$C\$5:\$C\$14, SMALL(IF(\$B\$16=\$B\$5:\$B\$14, ROW(\$B\$5:\$B\$14)-MIN(ROW(\$B\$5:\$B\$14))+1, “”), COLUMNS(\$A\$1:A1))) returns the book names based on the row index number returned by the SMALL function. • Press ENTER and drag the Fill Handle tool to the right. Finally, we will get the book names like the following figure. ## Practice Section

To practice by yourself, we have created a Practice section on the right side of each sheet. ## Conclusion  