Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

INDEX Function to Match & Return Multiple Values Vertically in Excel

In this article, I’ll demonstrate how to use the INDEX function to match and return multiple values vertically in Excel. We will go through 2 easy ways to do the task.
In the following picture, you can see the overview of the INDEX function to match and return multiple values vertically in Excel. Afterward, let’s dive into the article so that you can also do the task smoothly.

Overview of INDEX Function to Match & Return Multiple Values Vertically in Excel


Download Practice Workbook


2 Easy Ways to Use INDEX Function to Match & Return Multiple Values Vertically in Excel

In the following dataset, you can see the Country and City columns. After that, using this dataset, we will go through 2 easy methods to use the INDEX function to match and return multiple values vertically in Excel. Here, we used Excel 365. You can use any available Excel version.

Note: Since the VLOOKUP function can not return multiple values, it returns only the first values; so we used the INDEX function to do the task.

Dataset for INDEX Function to Match and Return Multiple Values Vertically in Excel

Read More: How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)


1. Using INDEX Function to Match and Return Multiple Values Vertically

In this method, we will use the INDEX function to match and return multiple values vertically in Excel. To execute the formula, we will use the IFERROR, SMALL, and ROW functions.

Steps:

First of all, we will type the following formula in cell C19.

=IFERROR(INDEX($C$5:$C$15,SMALL(IF($B$19=$B$5:$B$15,ROW($B$5:$B$15)-ROW($B$5)+1),ROW(1:1))),"")

Applying Formula for INDEX Function to Match and Return Multiple Values Vertically in Excel

Formula Breakdown

  • $C$5:$C$15 is the range for the value.
  • $B$19 is the lookup criteria.
  • $B$5:$B$15 are the range for the criteria.
  • ROW(1:1) indicates that the value will be returned vertically.
    • Output: New York

  • After that, press ENTER.
  • As a result, you will get the first city in the United States in cell C19.
  • Moreover, we will drag down the formula with the Fill Handle tool.

Using Fill Handle Tool

  • Therefore, you can see the complete City column.

Complete City Column

  • Afterward, you can also match for other countries by using the formula.
  • Furthermore, enter the country name in cell B19, it will automatically return the cities of the country in the City column.
  • In addition, see the following GIF.

GIF for INDEX Function to Match and Return Multiple Values Vertically in Excel

  • Moreover, If you want to use the INDEX function to match and return multiple values horizontally in Excel, then you use the following formula.
  • After that, drag the formula toward the right to get the complete City column.

INDEX Function to Match and Return Multiple Values horizontally in Excel

Read More: Excel INDEX-MATCH Formula to Return Multiple Values Horizontally

Similar Readings


2. Combining INDEX and AGGREGATE Functions to Match and Return Multiple Values Vertically

In this method, we will use the INDEX function and the AGGREGATE function to match and return multiple values vertically in Excel. Furthermore, we will describe the method.

Steps:

  • In the first place, type the following formula in cell C18.

=IFERROR(INDEX($C$5:$C$15,AGGREGATE(15,3,(($B$5:$B$15=$B$18)/($B$5:$B$15=$B$18)*ROW($B$5:$B$15))-ROW($B$4),ROWS($C$18:C18))),"")

Combining Functions

Formula Breakdown

  • $C$5:$C$15 is the range for the value.
  • $B$18 is the lookup criteria.
  • $B$5:$B$15 is the range for the criteria.
    • Output: New York

  • Afterward, press ENTER.
  • Hence, you will get the first city of the United States in cell C18.
  • Moreover, we will drag down the formula with the Fill Handle tool.

Employing Fill Handle Tool

  • Therefore, you can see the complete City column.

The complete City Column

Read More: Excel Index Match single/multiple criteria with single/multiple results


How to Use TEXTJOIN Function to Return Multiple Values in a Cell in Excel

The TEXTJOIN function can return multiple values in a single cell.

Steps:

  • First of all, type the formula in cell C18.

=TEXTJOIN(“,”,TRUE,IF(B5:B15=B18,C5:C15,””))

Inserting TECTJOIN Function

Formula Breakdown

  • B18 is the Criteria.
  • B5:B15 is the Range for matching criteria.
  • C5:C15 are the Range of the values.
  • TRUE means Ignoring all the empty cells.
  • TEXTJOIN(“,”,TRUE,IF(B5:B15=B18,C5:C15,””)) → therefore it becomes
    • Output: New York,Chicago,Dallas,Houston

  • Then, press ENTER.
  • Therefore, you can see the result in cell C18.

Result After Using TEXTJOIN Function

Read More: Excel INDEX MATCH If Cell Contains Text


Practice Section

You can download the above Excel file and practice the explained methods.

Practice Section


Conclusion

In this article, we show you 2 easy methods for the INDEX function to match and return multiple values vertically in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website Exceldemy for more related articles.


Related Articles

Prantick Bala

Prantick Bala

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.

6 Comments
  1. Hey there and many thanks for sharing your knowledge so generously.
    I have a complicated problem and lost in solutions, hoping you can help me with:
    Making it simple (hopefully), I want to count all occurrences of a certain code (there are different codes in table cells) in certain rows of the table. rows are selected based on a code in first column of the table.
    Hope I could describe it clear enough.
    Many thanks in advance

    • Thanks dar for finding the article helpful.
      Would you please share your Excel sheet?
      Then I’ll be able to understand what you are actually want to do and try my best to give you a solution.

  2. Is it possible to INDEX/MATCH/AGGFREGATE with multiple MATCH criteria, and then returning multiple values?

    • Hi Debbie,

      Yes, you can use multiple criteria for the MATCH function. Try it like this:
      MATCH(1,(first criteria)*(second criteria)*(third criteria),0)
      in place of the match function used for a single criterion.

  3. Hello,

    Can you pls help me with the above formula? I have 2 sheets of excel that I need to automatically copy vertically the input of a selected Customer data the lines it has active. What is the formula that should I use to auto-populate the information

    Sheet 1 -input that needs to be populated

    Customer Name- Cell C9

    List of products to be populated based on the Customer Name- B19, B20, B21, B22, etc

    Sheet 2 -data info

    All Customer Names-cell A3:A123

    Products of the customer name-Cell G3-G123

    Thank you,

    • Hi Elona,

      I am not sure I understand your problem quite clearly. But if you are having problems importing data from different sheets, then put the sheet name before the range in single quotes followed by an exclamation sign.

      For example, you should add ‘Sheet 2’! before the range G3:G123 or any other range while using them as the source to use formulas in the first sheet.

Leave a reply

ExcelDemy
Logo