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.

Dataset

Download Practice Workbook

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

VLOOKUP

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.

VLOOKUP VALUE

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

MATCH RETURN MULTIPLE VALUES

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

INDEX VALUE

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

INDEX VALUES

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.

CHANGING VALUES

 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

MATCH RETURN MULTIPLE VALUES

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

INDEX VALUE

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

index values

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

textjoin

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

MATCH RETURN MULTIPLE VALUES

4.   Filter Multiple Values Vertically

You can get the values vertically by using Filter. For that, first go to Home > Editing > Sort & Filter > 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.

FILTER MENU

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

MATCH RETURN MULTIPLE VALUES

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

INDEX AND AGGREGATE

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

MATCH RETURN MULTIPLE VALUES

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

MATCH AND RETURN

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

Leave a reply

ExcelDemy
Logo