Prantick Bala
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.
Download Practice Workbook
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.
Read More: How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)
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.
Read More: Excel INDEX-MATCH Formula to Return Multiple Values Horizontally
Similar Readings
- How to Select Specific Data in Excel (6 Methods)
- INDEX MATCH vs VLOOKUP Function (9 Examples)
- How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results
- Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)
- Index Match Multiple Criteria in Rows and Columns in Excel
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.
Read More: Excel Index Match single/multiple criteria with single/multiple results
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
- INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
- How to Use INDEX MATCH Formula in Excel (9 Examples)
- [Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)
- Examples with INDEX-MATCH Formula in Excel (8 Approaches)
- Excel INDEX MATCH to Return Multiple Values in One Cell
- How to Match Multiple Criteria from Different Arrays in Excel
- INDEX-MATCH with Duplicate Values in Excel (3 Quick Methods)
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.