How to VLOOKUP and Return Multiple Values Vertically in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook


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.

excel vlookup return multiple values vertically

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.

Applying a Combination of VLOOKUP and COUNTIF Functions to Return Multiple Values Vertically in Excel

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.

Applying a Combination of VLOOKUP and COUNTIF Functions to Return Multiple Values Vertically in Excel

This will generate a series of Book Types along with their serial numbers. Like Novel1, Novel2, etc…

result

  • 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.

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.

Applying a Combination of VLOOKUP and COUNTIF Functions to Return Multiple Values Vertically in Excel

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

Similar Readings


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.

Utilizing a Combination of INDEX, SMALL, and ROWS Functions to VLOOKUP and Return Multiple Values Vertically in 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”.

Utilizing a Combination of INDEX, SMALL, and ROWS Functions to VLOOKUP and Return Multiple Values Vertically in Excel

Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)


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.

Using FILTER Function to VLOOKUP and Return Multiple Values Vertically

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.

Using FILTER Function to VLOOKUP and Return Multiple Values Vertically

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


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.

VLOOKUP and Return Multiple Values Horizontally in Excel

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.

VLOOKUP and Return Multiple Values Horizontally in Excel


Practice Section

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

practice


Conclusion

In this article, we have discussed different ways to VLOOKUP and return multiple values vertically in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.


Related Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo