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.

## πΒ Download Excel File

Download the following Excel workbook to realize the topic more clearly.

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

**π Read More: 6 Methods to Match Multiple Criteria from Different Arrays in Excel**

**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!

## πΒ Takeaways from This Article

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

## Conclusion

That concludes the discussion for today. These are some convenient methods to find and return multiple corresponding values applying **INDEX **function in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website **ExcelDen** to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.

## Related Articles

**3 Examples to Use INDEX-MATCH Instead of VLOOKUP in Excel****10 Cases to SUM with INDEX-MATCH for Multiple Criteria in Excel**