Rifat Hassan
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.
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.
- Press ENTER and drag down the Fill Handle tool.
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
HereG4is theColumn Headerof theBook Type(Novel).C5:C14is the first column of my data set (Book Type).B4:E14is the whole data set, along with the extra column right in front of theBook Type3is the column number of the data that we want to extract(Book Namein this example) considering the extra (Helper) column.COUNTIF(C4:C13, G3)tells how many cells in the rangeC4:C13 (Book Type)contain the value in cellG3 (Novel).In simple words, how many novels there are in total? It is4.INDIRECT("A"&COUNTIF(C4:C13,G3))becomesINDIRECT("A"&4)and returns the cell referenceA4. Seethe INDIRECT functionfor details.ROW(A1:INDIRECT("A"&COUNTIF(C4:C13,G3)))now becomesROW(A1:A4).It returns an array from 1 to 7,{1, 2, 3, 4}. Seethe ROW functionfor details.G3&ROW(A1:INDIRECT("A"&COUNTIF(C4:C13,G3)))now concatenates the value in cellG3 (Novel)with the array returned by theROW 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)becomesVLOOKUP({Novel1, Novel2, ..., Novel7},B4:E13,3,FALSE).It searches for each value of the array{Novel1, Novel2, ..., Novel4}in the lookup columnB.Then returns the correspondingNameof the novel from the3rdcolumn (Column_Index_Numberis3.).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.
Read More: INDIRECT VLOOKUP in Excel
Similar Readings
- Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)
- Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
- How to Combine Excel SUMIF & VLOOKUP Across Multiple Sheets
- Excel VLOOKUP to Find Last Value in Column (with Alternatives)
- How to VLOOKUP with Multiple Conditions in Excel (2 Methods)
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 fromColumn BwithBook TypeNovel. For theNovel,we will haveTRUE.Output → {TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}ROW(B5:B14) → The ROW functionwill 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 rangeOutput → 4ROW(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)),"") →becomesIF({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)) →becomesSMALL({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 functionwill 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”.
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.
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.
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.
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
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
- VLOOKUP to Return Multiple Columns in Excel (4 Examples)
- How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)
- VLOOKUP with Numbers in Excel (4 Examples)
- How to Use VLOOKUP for Rows in Excel (With Alternatives)
- VLOOKUP with Multiple Criteria in Excel (6 Examples)
- VLOOKUP SUM Multiple Rows (4 Ways with Alternative)
- VLOOKUP Partial Match Multiple Values (3 Approaches)
