Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# INDEX Function to Match & Return Multiple Values Vertically in Excel

In this article, I’ll demonstrate how to use the INDEX function to match and return multiple values vertically in Excel. We will go through 2 easy ways to do the task.
In the following picture, you can see the overview of the INDEX function to match and return multiple values vertically in Excel. Afterward, let’s dive into the article so that you can also do the task smoothly. ## 2 Easy Ways to Use INDEX Function to Match & Return Multiple Values Vertically in Excel

In the following dataset, you can see the Country and City columns. After that, using this dataset, we will go through 2 easy methods to use the INDEX function to match and return multiple values vertically in Excel. Here, we used Excel 365. You can use any available Excel version.

Note: Since the VLOOKUP function can not return multiple values, it returns only the first values; so we used the INDEX function to do the task. ### 1. Using INDEX Function to Match and Return Multiple Values Vertically

In this method, we will use the INDEX function to match and return multiple values vertically in Excel. To execute the formula, we will use the IFERROR, SMALL, and ROW functions.

Steps:

First of all, we will type the following formula in cell C19.

`=IFERROR(INDEX(\$C\$5:\$C\$15,SMALL(IF(\$B\$19=\$B\$5:\$B\$15,ROW(\$B\$5:\$B\$15)-ROW(\$B\$5)+1),ROW(1:1))),"")` Formula Breakdown

• \$C\$5:\$C\$15 is the range for the value.
• \$B\$19 is the lookup criteria.
• \$B\$5:\$B\$15 are the range for the criteria.
• ROW(1:1) indicates that the value will be returned vertically.
• Output: New York

• After that, press ENTER.
• As a result, you will get the first city in the United States in cell C19.
• Moreover, we will drag down the formula with the Fill Handle tool. • Therefore, you can see the complete City column. • Afterward, you can also match for other countries by using the formula.
• Furthermore, enter the country name in cell B19, it will automatically return the cities of the country in the City column.
• In addition, see the following GIF. • Moreover, If you want to use the INDEX function to match and return multiple values horizontally in Excel, then you use the following formula.
• After that, drag the formula toward the right to get the complete City column. ### 2. Combining INDEX and AGGREGATE Functions to Match and Return Multiple Values Vertically

In this method, we will use the INDEX function and the AGGREGATE function to match and return multiple values vertically in Excel. Furthermore, we will describe the method.

Steps:

• In the first place, type the following formula in cell C18.

`=IFERROR(INDEX(\$C\$5:\$C\$15,AGGREGATE(15,3,((\$B\$5:\$B\$15=\$B\$18)/(\$B\$5:\$B\$15=\$B\$18)*ROW(\$B\$5:\$B\$15))-ROW(\$B\$4),ROWS(\$C\$18:C18))),"")` Formula Breakdown

• \$C\$5:\$C\$15 is the range for the value.
• \$B\$18 is the lookup criteria.
• \$B\$5:\$B\$15 is the range for the criteria.
• Output: New York

• Afterward, press ENTER.
• Hence, you will get the first city of the United States in cell C18.
• Moreover, we will drag down the formula with the Fill Handle tool. • Therefore, you can see the complete City column. ## How to Use TEXTJOIN Function to Return Multiple Values in a Cell in Excel

The TEXTJOIN function can return multiple values in a single cell.

Steps:

• First of all, type the formula in cell C18.

=TEXTJOIN(“,”,TRUE,IF(B5:B15=B18,C5:C15,””)) Formula Breakdown

• B18 is the Criteria.
• B5:B15 is the Range for matching criteria.
• C5:C15 are the Range of the values.
• TRUE means Ignoring all the empty cells.
• TEXTJOIN(“,”,TRUE,IF(B5:B15=B18,C5:C15,””)) → therefore it becomes
• Output: New York,Chicago,Dallas,Houston

• Then, press ENTER.
• Therefore, you can see the result in cell C18. Read More: Excel INDEX MATCH If Cell Contains Text

## Practice Section

You can download the above Excel file and practice the explained methods.

## Conclusion

In this article, we show you 2 easy methods for the INDEX function to match and return multiple values vertically in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website Exceldemy for more related articles.

## Related Articles #### Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

1. Reply Hey there and many thanks for sharing your knowledge so generously.
I have a complicated problem and lost in solutions, hoping you can help me with:
Making it simple (hopefully), I want to count all occurrences of a certain code (there are different codes in table cells) in certain rows of the table. rows are selected based on a code in first column of the table.
Hope I could describe it clear enough.

• Reply Thanks dar for finding the article helpful.
Then I’ll be able to understand what you are actually want to do and try my best to give you a solution.

2. Reply Is it possible to INDEX/MATCH/AGGFREGATE with multiple MATCH criteria, and then returning multiple values?

• Reply Hi Debbie,

Yes, you can use multiple criteria for the MATCH function. Try it like this:
MATCH(1,(first criteria)*(second criteria)*(third criteria),0)
in place of the match function used for a single criterion.

3. Reply Hello,

Can you pls help me with the above formula? I have 2 sheets of excel that I need to automatically copy vertically the input of a selected Customer data the lines it has active. What is the formula that should I use to auto-populate the information

Sheet 1 -input that needs to be populated

Customer Name- Cell C9

List of products to be populated based on the Customer Name- B19, B20, B21, B22, etc

Sheet 2 -data info

All Customer Names-cell A3:A123

Products of the customer name-Cell G3-G123

Thank you,

• Reply Hi Elona,

I am not sure I understand your problem quite clearly. But if you are having problems importing data from different sheets, then put the sheet name before the range in single quotes followed by an exclamation sign.

For example, you should add ‘Sheet 2’! before the range G3:G123 or any other range while using them as the source to use formulas in the first sheet. 