How to Use Excel VLOOKUP to Return Multiple Values Vertically

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.

Follow these steps:

Step 1: Let’s create the helper column first. Select cell B7 => Insert the following formula.

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.

Follow these steps:

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

Step 7: Press the Enter key to see an output like the following image.

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

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.

Follow these steps:

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.

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

Follow these steps:

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

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.

Read More: VLOOKUP to Return Multiple Values Horizontally in Excel

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.

Follow these steps:

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.

Follow these steps:

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

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.

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.

Follow these steps:

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.

Follow these steps:

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

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.

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.

Follow these steps:

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.

Follow these steps:

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

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.

Read More: How to Use VLOOKUP Function on Multiple Rows in Excel

Download Practice Workbook

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.

Related Readings

• How to VLOOKUP Multiple Values in One Cell in Excel
• Excel VLOOKUP to Return Multiple Values in One Cell Separated by Comma
• Excel VLOOKUP Function to Return Min Value from Multiple Hits
• Find Max of Multiple Values by Using VLOOKUP Function in Excel

<< Go Back to VLOOKUP Multiple Values | Excel VLOOKUP Function | Excel Functions | Learn Excel