# Index Function to Match and Return Multiple Values Vertically in Excel

You cannot use the VLOOKUP function to match and return multiple values in Excel. In this article, I’ll demonstrate how to use the INDEX function to match and return multiple values vertically and horizontally. I’ll also show you some other ways to do the task.

Let’s say, we have the name of multiple cities of different countries in our dataset. Now we want to get the name of cities in a column or row for any specific country.

## Index Function to Match and Return multiple Values Vertically and Other Cases

### 1.   What Will Happen If We Use VLOOKUP Function?

First, let’s look at what will happen if we want to match and return multiple values by using the VLOOKUP function. To match the country United States and return cities of this country, type the following formula in cell E5,

`=VLOOKUP(D5,A5:B15,2,FALSE)`

Here, D5= Lookup value

A5:B15 = lookup range

2 = Lookup column of lookup range

FALSE = Exact match

After pressing ENTER, we will get only the first city’s name. That means VLOOKUP can not return multiple values, it returns only the first values. So, we can not get multiple values vertically by using the VLOOKUP  function.

### 2.   INDEX function to Match and Return Multiple Values

#### 2.1 Return Values Vertically

The INDEX function can match and return multiple values vertically. Type the formula in cell E5,

`=IFERROR(INDEX(\$B\$5:\$B\$15,SMALL(IF(\$D\$5=\$A\$5:\$A\$15,ROW(\$A\$5:\$A\$15)-ROW(\$A\$5)+1),ROW(1:1))),"")`

Here, \$B\$5:\$B\$15 =range for the value

\$D\$5 = lookup criteria

\$A\$5:\$A\$15 = range for the criteria

ROW(1:1) indicates that the value will be returned vertically

After pressing ENTER you will get the first city of the United States in cell E5.

Now drag cell E5 vertically downward, you will get all the cities of the United States in column E.

You can also match for other countries by using the formula. Enter the country name in cell D5, it will automatically return the cities of the country in column D.

#### 2.2 Return Values Horizontally

The INDEX function can also return values horizontally. Type the formula in cell E5,

`=IFERROR(INDEX(\$B\$5:\$B\$15,SMALL(IF(\$D\$5=\$A\$5:\$A\$15,ROW(\$A\$5:\$A\$15)-ROW(\$A\$5)+1),COLUMN(A1))),"")`

Here, \$B\$5:\$B\$15 =range for the value

\$D\$5 = lookup criteria

\$A\$5:\$A\$15 = range for the criteria

COLUMN(A1) indicates that the value will be returned horizontally

After pressing ENTER, you will get the first city in the United States.

Now drag the cell E5 horizontally, you will get all the cities of the United States in Row 5.

### 3.   TEXTJOIN Function to Return Multiple Values in a Cell

The TEXTJOIN function can return multiple values in a single cell. Type the formula in cell E5,

`=TEXTJOIN(",",TRUE,IF(A5:A15=D5,B5:B15,""))`

Here,  D5= Criteria

A5:B15 =  Range for matching criteria

B5:B15 = Range of the values

TRUE = Ignoring all the empty cells

After pressing ENTER, you will get all the cities of the United States in cell E5.

Read More: Excel INDEX MATCH If Cell Contains Text

### 4.   Filter Multiple Values Vertically

You can get the values vertically by using Filter. For that, first go to Home > Editing > Sort & Filter > Filter.

Now a little downward arrow will be shown besides all the column header. Click on the arrow besides Country. A dropdown menu will appear. From this menu select the United States only and click on OK.

Now In your dataset, you will see only the cities of the United States.

### 5.   Index and Aggregate to Match and Return Multiple Values Vertically

The INDEX function and The AGGREGATE function together can match and return multiple values vertically in Excel. Type the formula in cell E5,

`=IFERROR(INDEX(\$B\$5:\$B\$15,AGGREGATE(15,3,((\$A\$5:\$A\$15=\$D\$5)/(\$A\$5:\$A\$15=\$D\$5)*ROW(\$A\$5:\$A\$15))-ROW(\$A\$4),ROWS(\$E\$5:E5))),"")`

Here, \$B\$5:\$B\$15 =range for the value

\$D\$5 = lookup criteria

\$A\$5:\$A\$15 = range for the criteria

After pressing ENTER, you will get the first city of the United States in cell E5.

Now drag the cell E5 vertically downward, you will get all the cities of the United States in column E.

## Conclusion

You can use any of the described methods to match and return multiple values vertically, but using the INDEX function is the most convenient way. If you face any confusion about any of the methods please leave a comment.

## Related Articles

#### Prantick

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