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