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.
INDEX Function to Match & Return Multiple Values Vertically in Excel: 2 Easy Ways
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 Microsoft 365. You can use any available Excel version.
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.
Practice Section
You can download the above Excel file and practice the explained methods.
Download Practice Workbook
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.
<< Go Back to INDEX MATCH | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
Many thanks in advance
Thanks dar for finding the article helpful.
Would you please share your Excel sheet?
Then I’ll be able to understand what you are actually want to do and try my best to give you a solution.
Is it possible to INDEX/MATCH/AGGFREGATE with multiple MATCH criteria, and then returning multiple values?
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.
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,
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.