# Use INDEX Function to Match and Return Multiple Values Vertically

Using VLOOKUP function is an outstanding way to search for a value in a dataset and return the corresponding value from an adjacent column. However, if you have multiple corresponding values, the VLOOKUP function will keep showing the first instance repeatedly. In this case, applying INDEX function in order to match corresponding values and return those values verticallyΒ is a handy way. Today we will describe how to match and return multiple values vertically using INDEX function in Excel.

## Learn to Use INDEX Function to Match and Return Multiple Values Vertically in Excel with These 4 Suitable Approaches

In this article, we will use the following dataset of best-selling cars in 2021 to match and return several values vertically.

Approach

### 1. Combining INDEX and SMALL Functions

In our first approach, we will describe how to match several values and return those vertically by coalescing IFERROR, INDEX, SMALL, IF, and ROW functions.

β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ

• Initially, select cell F6, and write the name of the Manufacturer. In our case, we wrote Toyota.
• Next, in cell G6, type inΒ the formula, and press Enter:

=IFERROR(INDEX(\$C\$6:\$C\$17,SMALL(IF(\$F\$6=\$B\$6:\$B\$17,ROW(\$B\$6:\$B\$17)-ROW(\$B\$6)+1),ROW(1:1))),ββ)

• Now use the Fill Handle icon to apply the formula to the column.

• Voila! Consequently, we will find the Models of Toyota in the G column.

• Afterward, if we change the Manufacturer in cell F6 to Honda, we will get the Models of Honda appearing in the dataset.

π¨ Formula Breakdown

IFERROR(INDEX(\$C\$6:\$C\$17,SMALL(IF(\$F\$6=\$B\$6:\$B\$17,ROW(\$B\$6:\$B\$17)-ROW(\$B\$6)+1),ROW(1:1))),ββ)

πΒ  First, letβs see what the IF function does.

IF(\$F\$6=\$B\$6:\$B\$17,ROW(\$B\$6:\$B\$17)-ROW(\$B\$6)+1)

πΒ  The IF function will take the value stored in cell F6 and match it from cells B6:B17. Wherever the IF function finds the value stored in cell F6, it will return TRUE else it will return FALSE.

πΒ  The first ROW function, ROW(\$B\$6:\$B\$17), returns the number of rows of those cells which are 6,7,8β¦.17. ROW(\$B\$6) returns 6.

πΒ  Finally, from ROW(\$B\$6:\$B\$17)-ROW(\$B\$6)+1, we get the serial number of our dataset. For instance, Ford is on serial number 1 in our dataset. We could also write ROW(\$B\$6:\$B\$17)-ROW(\$B\$5) for the same outcome.

SMALL(IF(\$F\$6=\$B\$6:\$B\$17,ROW(\$B\$6:\$B\$17)-ROW(\$B\$6)+1),ROW(1:1))

πΒ  The SMALL function gets an array of TRUE or FALSE from the IF function. It returns the serial number where it finds TRUE first in that array because ROW(1:1) depicts 1 in cell G6. Similarly, in cell G7, it returns the second instance.

π Β The INDEX function returns the corresponding value of the serial number returned by the SMALL function from the C Column.

πΒ  We used the IFERROR function to avoid #NUM! error as all the manufacturers do not have the same number of models in our dataset.

Note:

For older Excel versions eg. 2019, press Ctrl+Shift+Enter to apply the formula.

π Read More: 3 Easy Ways to Use INDEX-MATCH Across Multiple Sheets

Approach

### 2. Merging INDEX and AGGREGATE Functions

In our second approach, we will use the combination of INDEX and AGGREGATE functions to match and return multiple values vertically.

Β β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ

• Just like the previous method, put the Manufacturerβs name in cell F6 and Enter the formula below in cell G6.

=IFERROR(INDEX(\$C\$6:\$C\$17,AGGREGATE(15,3,((\$B\$6:\$B\$17=\$F\$6)/(\$B\$6:\$B\$17=\$F\$6)*ROW(\$B\$6:\$B\$17))-ROW(\$B\$5),ROW(1:1))),ββ)

• Again, use the Fill Handle icon.
• As a result, you will get the most sold Models by Toyota.

• Next, you can change the name of the Manufacturer to Chevrolet to see Silverado in cell G6.

π¨ Formula Breakdown

IFERROR(INDEX(\$C\$6:\$C\$17,AGGREGATE(15,3,((\$B\$6:\$B\$17=\$F\$6)/(\$B\$6:\$B\$17=\$F\$6)*ROW(\$B\$6:\$B\$17))-ROW(\$B\$5),ROW(1:1))),ββ)

πΒ  Here, as we wrote 15 as the function number, the AGGREGATE function brings the SMALL function.

πΒ  We chose option number 3 to ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions.

(\$B\$6:\$B\$17=\$F\$6)/(\$B\$6:\$B\$17=\$F\$6)

πΒ  We get an array of 1 and #DIV/0! here. Wherever it finds the manufacturer stored in cell F6 from the dataset, it returns 1 otherwise #DIV/0!.

πΒ  ROW(\$B\$6:\$B\$17) multiplies the row number with that array. If we subtract ROW(\$B\$5) we will get the serial number just like the first approach.

πΒ  Later, the AGGREGATE function returns the serial number where it found the manufacturer stored in cell F6 first since ROW(1:1) returns 1 in cell G6.

πΒ  The INDEX function returns the corresponding value of the serial number returned by the AGGREGATE function from the C Column.

πΒ  The IFERROR function is used to avoid #NUM! error.

Approach

### 3. Using TEXTJOIN Function

In our next approach, we will show how to match multiple values and return those values in one cell separated by delimiters using TEXTJOIN function.

β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ

• In the beginning, type in the name of the Manufacturer in cell C19 and Enter the formula below in cell C20.

=TEXTJOIN(β,β,TRUE,IF(B6:B17=C19,C6:C17,ββ))

• Done! Immediately, you will get the names of the Models in cell C20.

π¨ Formula Breakdown

TEXTJOIN(β,β,TRUE,IF(B6:B17=C19,C6:C17,ββ))

πΒ  The IF function checks the name of the Manufacturer stored in cell C19 from cells B6:B17 and returns the corresponding Model from cells C6:C17. If the function does not find a match, it will return an empty string.

π Β The TEXTJOIN function joins the Models returned by the IF function with the Delimiter β,β.

Note:

For older Excel versions eg. 2019, press Ctrl+Shift+Enter to apply the formula.

Approach

### 4. Implementing Filter Option

In our final method, we will simply use the Filter option from the Excel Ribbon to return multiple values.

β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ

• First, select cells B5 to D17.
• Later, navigate to the Data tab, and click Filter from the Sort & Filter group.

• Momentarily, you will notice drop-down icons on the header row.

• After that, click the drop-down icon from the Manufacturer Then, select the Manufacturer you want to filter out.

• Youβre all set!

πΒ  Firstly, we demonstrated how to find and return several corresponding values by utilizing INDEX and SMALL functions.

πΒ  Secondly, we merged INDEX and AGGREGATE functions to find multiple values and return those values.

πΒ  Later, we used the TEXTJOIN function to show multiple values in a cell.

πΒ  Lastly, we described how to use the Filter option from the Excel Ribbon to show multiple values.