One of the most amazing and widely used functions of Excel is the VLOOKUP function. So far we have learned to pull out a single match from a range of cells using the VLOOKUP function. In this article, I’ll show you how to return multiple values vertically from a range of cells using the VLOOKUP function in Excel.

Download Practice Workbook

How to Return Multiple Values Vertically Using VLOOKUP in Excel

Here we’ve got a data set with the Book Types, Book Names, and Authors of some books of 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 the VLOOKUP function of Excel.

Step 1:

• Select a new column left to the first column of your data set.

• Enter the following formula in the first cell:

[Here C4 and C13 are respectively the first and last cells of my first column. You use your one.]

Step 2:

• Then drag the Fill Handle up to the last cell.
• This will generate a series of Book Types along with their serial numbers. Like Novel1, Novel2, etc…

Step 3:

• Now, go to another new column.
• Put the name of the Book Type that you want to look up as the Column Header.

Step 4:

• Enter this formula in the first cell of the column:

• Here G3 is the Column Header of the Book Type (Novel).
• C4:C13 is the first column of my data set (Book Type)
• B4:B13 is the whole data set, along with the extra column right in front of the Book Type column.
• 3 is the column number of the data that we want to extract (Book Name in this example) considering the extra column.
• It is an Array Formula. So don’t forget to select multiple cells earlier and press CTRL+SHIFT+ENTER unless you are in Office 365.

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 the multiple matches in reverse order.
• It displays the last novel “The Forty Rules of Love” first, the first novel “A Tale of Two Cities” last, and so on.

Explanation of the Formula

• 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. See the COUNTIF function for details.
• 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.

Alternative Methods to the VLOOKUP to Return Multiple Values Vertically

1. Use FILTER Function to Return Multiple Values in the New Versions of Excel

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.

The formula to find out all the books of type “Novel” will be:

• Here C4:C13 is the column from which I want to extract data (Book Name).
• B4:B13 is the first column of my data set (Book Type).
• And F3 is the Column Header containing the type of book I want to extract (Novel).

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.

2. Combine IFERROR, INDEX, SMALL, ROW, and ROWS Function for Older Versions of Excel

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.

The formula to get all the books of type novel in the data set, the formula will be:

• Here, B4:D13 is my data set.
• B4:B13 is the first column of my data set.
• F3 is the Column Header containing the Book Type (Novel).
• B1:B3 is the cells of my first column before the data set starts.
• It is an Array Formula. So don’t forget to select multiple cells beforehand and press CTRL+SHIFT+ENTER unless you are in Office 365.

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.

Conclusion

Using these methods, you can return multiple values vertically from a range of cells using the VLOOKUP function of Excel, or in some alternative ways. Do you know any other method? Or do you have any questions? Feel free to ask us.

Related Readings

VLOOKUP to Return Multiple Columns in Excel

VLOOKUP Example between Two Sheets in Excel

How to Use Indirect VLOOKUP in Excel

VLOOKUP to Return All Matches in Excel

VLOOKUP Fuzzy Match in Excel

The post Excel VLOOKUP to Return Multiple Values Vertically appeared first on ExcelDemy.