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
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 functionfor 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 functionfor 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.
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 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”.
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)