# Using the INDEX Function to Match & Return Multiple Values Vertically in Excel – 2 Methods

This is an overview of the INDEX function.

The sample dataset contains Country and City columns.

### Method 1 – Using the INDEX Function to Match and Return Multiple Values Vertically

Uuse the INDEX,Â IFERROR, SMALL, and ROW functions.

Steps:

Enter the following formula in 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.
• \$B\$19 is the lookup criteria.
• \$B\$5:\$B\$15 is the range for the criteria.
• ROW(1:1) indicates that the value will be returned vertically.
• Output: New York
• Press ENTER.

You will get see the first city in the United States in C19.

• Drag down the Fill Handle to see the result in the rest of the cells.

• This is the output.

You can apply the formula to other countries:

• Enter the country name in B19, it will automatically return the cities in the City column.
• Observe the GIF.

To use the INDEX function to match and return multiple values horizontally, use the following formula:

• Drag the formula to the right to see the CityÂ column.

### Method 2 – Combining the INDEX and the AGGREGATE Functions to Match and Return Multiple Values Vertically

Use the INDEXÂ and the AGGREGATE functions.

Steps:

• Enter the following formula in 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
• Press ENTER.

You will see the first city in the United States in C18.

• Drag down the Fill Handle to see the result in the rest of the cells.

• This is the output.

## How to Use the TEXTJOIN Function to Return Multiple Values in a Cell in Excel

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

Steps:

• Enter the formula in C18.

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

Formula Breakdown

• B18 is the Criteria.
• B5:B15 is the Range to match the criteria.
• C5:C15 is the Range of values.
• TRUE Â ignores all empty cells.
• TEXTJOIN(“,”,TRUE,IF(B5:B15=B18,C5:C15,””)) â†’ it becomes
• Output: New York,Chicago,Dallas,Houston
• Press ENTER.

You can see the result in C18.

## Practice Section

<< Go Back to INDEX MATCH | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

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

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

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

Advanced Excel Exercises with Solutions PDF