# How to Use the Excel VLOOKUP Function to Return Multiple Values Vertically – 5 Methods

Problems with VLOOKUP When Returning Multiple Matches:

In the dataset below there are three columns: Book Type, Book Name, and Author.

To get the names of all autobiographies using the VLOOKUP formula, you will get only one book name when there are 2.

In the following image the formula returned the correct result.

Overview of Excel VLOOKUP to Return Multiple Values Vertically

## Method 1 – Combining the VLOOKUP, COUNTIF, ROW and INDIRECT Functions to Return Multiple Values Vertically

Combine the VLOOKUP, COUNTIF, ROW, and INDIRECT functions to return multiple values vertically using a helper column.

The dataset hasÂ  three columns: Book Type, Book Name, and Author.

In G7, choose a book type, and get all books of that type in a vertical List.

Step 1: Insert a helper column. Select B7 and enter the following formula.

`=C7&COUNTIF(C7:\$C\$16,C7)`

Step 2: Press Enter.

You will see Novel4 in B7.

Step 3: Place the cursor at the bottom right corner of B7. You will see the Fill Handle icon.

Step 4: Drag down the Fill Handle to see the result in the rest of the cells. The helper column is created.

Step 5: Select G7. Create a drop-down to choose the book type . Click the drop-down and choose Novel.

Use Excel VLOOKUP to return multiple book names vertically.

Step 6: Choose G11 and enter the following formula.

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

Step 7: Press Enter to see the output.

Note

A column header was created for the Book List column. It will change based on the value entered in G7. Conditional Formatting is used to highlight and create borders in the output.

Step 8: If you change the book type in the drop-down list, the names will be returned and highlighted.

Observe the GIF.

Note

The helper column was hidden.

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 G7 is found in C7:C16. For example, if the value of G7Â 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 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 in column D.

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

## Method 2 – Using the VLOOKUP, FILTER, and IFS Functions to Return Multiple Values Vertically

Combine the VLOOKUP, FILTER, and IFS functions.

The dataset showcases Book Type, Book Name, and Sales Quantity. In F8, choose the criteria using a dynamic drop-down. In G8, enter the sales quantity values for comparison.

Step 1: Select G7 => Enter 123 in Sales Quantity.

Step 2: Select F7 => Create a drop-down to choose <, >, =, or <> signs => Expand the drop-down => Choose Greater Than (>) sign.

Note

While creating theÂ  drop-down, do not enter the Equal (=) sign at the beginning in the Source field.

Step 3: Select F10 => Enter 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 Enter=> You will get multiple book names vertically for sales quantity greater than 123.

Step 5: If you change the criteria in sales quantity, the VLOOKUP formula will return the book names vertically.

Observe the GIF.

## Method 3 – Combining the VLOOKUP, FILTER, ISNUMBER and SEARCH Functions to Return Multiple Values Vertically

Use the VLOOKUP,Â FILTER, ISNUMBER and SEARCH functions.

To get multiple book names if their type contains specific letters or words and vice versa. In F7 and H7, enter words, and based on these words, the book names will be displayed in the corresponding columns.

Step 1: Select F7 => Enter Poet.

Step 2: Choose H7 => Enter Biography.

Step 3: Select F11 => Enter 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 H11 =>Use the following formula.

`=VLOOKUP(FILTER(C7:C16, ISNUMBER(SEARCH(H7, B7:B16))=FALSE), C7:D16, 1, FALSE)`

Step 6: Press Enter => You will see a list of book names that do not contain the word Biography in their type.

Step 7: If you change the words in F7 and H7, the formulas will return the book names vertically, as shown in the following GIF.

## Method 4 – Using the VLOOKUP, FILTER, LEFT, and RIGHT Functions to Return Multiple Values Vertically

Use the VLOOKUP, FILTER, LEFT, RIGHT, andÂ  IFERROR functions to return multiple values vertically.

To get multiple book names if their type begins and ends with particular letters or words, in F7 and H7, enter some letters, and based on these letters, the book names will be displayed.

Step 1: Select F7 => Enter Auto.

Step 2: Choose H7 => Enter Fiction.

Step 3: Select F11 => use the following formula.

`=IFERROR(VLOOKUP(FILTER(C7:C16, LEFT(B7:B16,LEN(F7))=F7),C7:D16,1,FALSE),"")`

Step 4: Press Enter => You will see a list of book names that begin with the word Auto.

Step 5: Select H11 => Enter the following formula.

`=IFERROR(VLOOKUP(FILTER(C7:C16,RIGHT(B7:B16,LEN(H7))=H7),C7:D16,1,FALSE),"")`

Step 6: Press the EnterÂ => You will see a list of book names ending with Fiction.

Step 7: If you change the words in F7 and H7, the formulas will return the book names vertically, as shown in the following GIF.

## Method 5 – Using the INDEX, SMALL, ROW and ROWS Functions Instead of the VLOOKUP to Return Multiple Values Vertically

Combine the INDEX, SMALL, ROW and ROWS functions.

The dataset showcases the columns: Book Type, Book Name and Author. In F5, choose a book type using a drop-down list. When a book type is selected, the formula will display the book names in the Book List column.

Step 1: Select F5 => Create a drop-down => Expand the drop-down => ChooseÂ Poetry.

Step 2: Select F9 => Enter 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 3: Press Enter => You will see a list of poetry book names.

Step 4: If you change the book type in F5, the formula will return the 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)`

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

1 Comment
1. 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.

Advanced Excel Exercises with Solutions PDF