# How to Use Excel VLOOKUP to Return Multiple Values Vertically

Get FREE Advanced Excel Exercises with Solutions!

Excel VLOOKUP function returns only one result that matches the lookup criteria. So, you will get stuck when you have multiple matching values and try to get all of them using the Excel VLOOKUP function alone. However, it’s possible to get multiple matching values when the Excel VLOOKUP function is combined with some other Excel functions.

Problem with VLOOKUP When Returning Multiple Matches:

Let’s make it clear with an example. Assume like the following image, I have a dataset with three columns titled Book Type, Book Name, and Author. Now, I want to get the names of all the autobiography-type books using VLOOKUP alone. When you use the normal VLOOKUP formula, you will get results like the image, only one book name when there are 2 actually. In this free Excel tutorial, you will learn to use the Excel VLOOKUP function to return multiple values vertically.

Now, look at the following image. We solved the previous problem that occurred with VLOOKUP. Here, you can see our formula is giving us the exact result that we needed. Overview of Excel VLOOKUP to Return Multiple Values Vertically

In this article, we will return multiple values vertically using the following Excel formulas:

– VLOOKUP, COUNTIF, Row and INDIRECT Functions
– VLOOKUP, FILTER, and IFS Functions
– VLOOKUP, FILTER, ISNUMBER and SEARCH Functions
– VLOOKUP, FILTER, and IFERROR Functions
– INDEX, SMALL, ROW and ROWS Functions

Note: While writing this article, I used Microsoft Excel 365. However, all these methods can be applicable in Excel 2019 and Excel 2021.

Returning multiple values vertically can be useful in many cases. Suppose you are managing an inventory where you need to display a list of products that are out of stock. For education purposes, you may need to find the names of all the students who have taken a particular course. By combining VLOOKUP with other functions, you can easily do these types of work.

## 1. Combining VLOOKUP, COUNTIF, ROW and INDIRECT Functions to Return Multiple Values Vertically

Here, we will combine Excel VLOOKUP, COUNTIF, ROW, and INDIRECT functions to return multiple values vertically using a helper column.

In the following image, I have a dataset with three columns titled Book Type, Book Name, and Author. In cell G7, I will choose a book type, and when the book type is selected, all the books of that Book Type will be shown vertically under the Book List. Step 1: Let’s create the helper column first. Select cell B7 => Insert the following formula.

`=C7&COUNTIF(C7:\$C\$16,C7)` Step 2: Press the Enter key => You will see Novel4 in cell B7. Step 3: Hover the cursor over the bottom right corner of cell B7 => You will see the Fill Handle icon. Step 4: Drag the Fill Handle icon to cell B16 to copy the formula in other cells. You see, the helper column is created. Step 5: Select cell G7 => Create a drop-down to choose book type => Expand the drop-down => Choose Novel. Now, we will use Excel VLOOKUP to return multiple book names vertically.

Step 6: Choose cell G11 => Insert the following formula.

`=VLOOKUP(G7&ROW(B1:INDIRECT("A"&COUNTIF(C7:C16,G7))),B7:E16,3,FALSE)` Step 7: Press the Enter key to see an output like the following image. Note

We have created a column header for the Book List column that will change based on the value you input in Cell G7. We also used Conditional Formatting for the output. Basically, it will highlight and create borders automatically for those cells that have a value.

Step 8: If you change the book type from the drop-down, the names will be returned and highlighted like the following GIF. Note

We have hidden the helper column so the worksheet looks better.

Formula Breakdown

=VLOOKUP(G7&ROW(B1:INDIRECT(“A”&COUNTIF(C7:C16,G7))),B7:E16,3,FALSE)

=VLOOKUP(G7&ROW(B1:INDIRECT(“A”&2)),B7:E16,3,FALSE) // COUNTIF(C7:C16,G7) counts if the value of cell G7 is found in range C7:C16. For example, if the value of the G7 cell is Autobiography, we will get 2.

=VLOOKUP(G7&ROW(B1:INDIRECT(“A”&2)),B7:E16,3,FALSE)

=VLOOKUP(G7&ROW(B1:0),B7:E16,3,FALSE) // INDIRECT(“A”&1)) returns the values of cell A1.

=VLOOKUP(G7&ROW(B1:0),B7:E16,3,FALSE)

=VLOOKUP(G7&{1;2},B7:E16,3,FALSE) // ROW(B1:0) returns {1;2}.

=VLOOKUP(G7&{1;2},B7:E16,3,FALSE)

=VLOOKUP({“Autobiography1″;”Autobiography2”},B7:E16,3,FALSE) // G7&1 returns {“Autobiography1″;”Autobiography2”}.

=VLOOKUP({“Autobiography1″;”Autobiography2”},B7:E16,3,FALSE)

={“My Side”;”Playing It My Way”} // VLOOKUP({“Autobiography1″;”Autobiography2”}, B7:E16,3,FALSE) searches “Autobiography1” and “Autobiography2” in column B and returns the corresponding values of the D column.

Read More: How to VLOOKUP and Return Multiple Values in Drop Down List

## 2. Using VLOOKUP, FILTER, and IFS Functions to Return Multiple Values Vertically

Here, you will learn to combine the VLOOKUP, FILTER, and IFS functions to return multiple values vertically. We will return some values when the criteria are equal, not equal, greater, or smaller than a particular value.

Assume, like the screenshot below, I have a dataset with three columns titled Book Type, Book Name, and Sales Quantity. In cell F8, I want to choose a criteria using a dynamic drop-down. In cell G8, I will input sales quantity values for comparison. Throughout the Book List column, I will return multiple book names vertically. First, we will create a drop-down to choose the criteria and insert the sales quantity.

Step 1: Select cell G7 => Type 123 as an input of Sales Quantity. Step 2: Select cell F7 => Create a drop-down to choose the <, >, =, or <> signs => Expand the drop-down => Choose the Greater Than (>) sign. Note

While creating a drop-down, ensure not to type the Equal (=) sign at the beginning when inserting items in the Source field.

Now, we will return multiple book names vertically in the Book List column.

Step 3: Select the F10 cell => Insert the following formula.

`=VLOOKUP(FILTER(C7:C16,IFS(F7="=",D7:D16=G7,F7="<",D7:D16<G7,F7=">",D7:D16>G7,F7="<>",D7:D16<>G7)),C7:D16,1,FALSE)` Step 4: Press the Enter key => You will get multiple book names vertically for the sales quantity greater than 123. Step 5: If we change the criteria or sales quantity, the VLOOKUP formula will return the book names vertically, like the following GIF. ## 3. Joining VLOOKUP, FILTER, ISNUMBER and SEARCH Functions to Return Multiple Values Vertically

Here, you will learn how to return multiple values vertically based on partial match joining VLOOKUP with FILTER, ISNUMBER and SEARCH functions.

Here, I am using the same dataset as the previous method. However, I will return multiple book names if their type contains particular letters or words and vice versa. In the F7 and H7 cells, I will input words, and based on these words, the intended book names will appear in the corresponding columns. First, we will insert the words to check whether the book types contain these letters or not.

Step 1: Select cell F7 => Type Poet as an input. Step 2: Choose cell H7 => Type Biography as an input. Now, we will use the two VLOOKUP formulas to return multiple book names if their type contains and does not contain a particular term.

Step 3: Select cell F11 => Insert the following formula.

`=VLOOKUP(FILTER(C7:C16, ISNUMBER(SEARCH(F7, B7:B16))), C7:D16, 1, FALSE)` Step 4: Press Enter => You will see a list of book names that contain the text Poet in their type. Step 5: Select cell H11 => Insert the following formula.

`=VLOOKUP(FILTER(C7:C16, ISNUMBER(SEARCH(H7, B7:B16))=FALSE), C7:D16, 1, FALSE)` Step 6: Press the Enter key => You will see a list of book names that do not contain the term or word Biography in their type. Step 7: If we change the words in the F7 and H7 cells, these formulas will return the intended book names vertically, as shown in the following GIF. ## 4. Using VLOOKUP, FILTER, LEFT, and RIGHT Functions to Return Multiple Values Vertically

In this section, you will learn how to use the VLOOKUP, FILTER, LEFT, RIGHT, and  IFERROR functions to return multiple values vertically. This time, we will return some values that begin and end with a particular text or words.

Assume I am using the same dataset as the previous method. But here, I will return multiple book names if their type begins and ends with particular letters or words. In the F7 and H7 cells, I will input some letters, and based on these letters, the intended book names will appear in the corresponding columns. First, we will insert the text in the input cells to check whether the book types begin or end with these letters.

Step 1: Select cell F7 => Type Auto as an input. Step 2: Choose cell H7 => Type Fiction as an input. Now, we will use the two VLOOKUP formulas to return multiple book names if their type contains and does not contain a particular word.

Step 9: Select cell F11 => Insert the following formula.

`=IFERROR(VLOOKUP(FILTER(C7:C16, LEFT(B7:B16,LEN(F7))=F7),C7:D16,1,FALSE),"")` Step 10: Press Enter => You will see a list of book names that begin with the word Auto in their type. Step 11: Select cell H11 => Insert the following formula.

`=IFERROR(VLOOKUP(FILTER(C7:C16,RIGHT(B7:B16,LEN(H7))=H7),C7:D16,1,FALSE),"")` Step 12: Press the Enter key => You will see a list of book names ending with Fiction in their type. Step 13: If we change the words in the F7 and H7 cells, these formulas will return the intended book names vertically, as shown in the following GIF. ## 5. Combining INDEX, SMALL, ROW and ROWS Functions Instead of VLOOKUP to Return Multiple Values Vertically

Here, you will learn how to combine the INDEX, SMALL, ROW and ROWS functions to return multiple values vertically in Excel.

Like the following image, I have a dataset with three columns titled Book Type, Book Name and Author. In cell F5, I will choose a book type using a drop-down. When a book type is selected, the formula will display the intended book names in the Book List column. First, we will insert criteria in the cell F5.

Step 1: Select cell F5 => Create a drop-down => Expand the drop-down => Choose the Poetry option. Now, we will use the formulas to return multiple book names when the criteria match.

Step 9: Select cell F9 => Insert the following formula.

`=IFERROR(INDEX(B5:D14,SMALL(IF(B5:B14=F5,(ROW(B5:B14)-ROWS(B1:B4)),""),(ROW(B5:B14)-ROWS(B1:B4))),2),"")` Step 10: Press Enter => You will see a list of poetry-type book names. Step 11: If we change the book type in the F5 cell, these formulas will return the intended book names vertically, as shown in the following GIF. Note

You can also use the FILTER function to return multiple values vertically.

Excel Formula:

`=FILTER(C5:C14,B5:B14=F5)` So, in this Excel tutorial, we present several Excel VLOOKUP formulas and their purposes to return multiple values vertically. For example, To return multiple values vertically – using VLOOKUP, COUNTIF, Row and INDIRECT Functions when a criteria match, combining VLOOKUP, FILTER, and IFS Functions when a value is Equal, Not Equal, Smaller or Greater Than, joining VLOOKUP, FILTER, ISNUMBER and SEARCH Functions if a text contains or does not contain, using VLOOKUP, FILTER, and IFERROR Functions if a word begins or ends with. Do not hesitate to leave a comment if you have any questions.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has been working with the ExcelDemy project for more than 1 year. He has written 50+ articles and provided solutions of 60+ comments for ExcelDemy. Currently, he is working as an Excel & VBA Developer and also provides support and solutions in the ExcelDemy Forum. His work and learning interests are in developing various Excel & VBA applications. Outside of work, he enjoys Chess a lot. He is a founding Jahangirnagar University Chess Club member and an internationally rated chess player.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  