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

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

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

We will be happy to hear your thoughts