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.

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.

`=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.**

**Follow these steps**:

** 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.**

**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.

**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.**

**Follow these steps**:

** 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**.

**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.

`=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.**

**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.

`=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.**

**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.

`=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)`

**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**

In you example, if the helper column only contains one value rather than multiples, it fills the entire search column with the same find. Any ideas as to how to fix this.

Go to the tab “When a criteria matches” and select ‘Non-Fiction’ to see what I mean. When there are multiple helpers for the book name it works fine.