Using the INDEX Function to Match and Return Multiple Values Vertically in Excel – 2 Methods

This is an overview of the INDEX function.

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


The sample dataset contains 2 columns: Country and City.

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


Method 1 – Using the INDEX Function to Match and Return Multiple Values Vertically

Steps:

Enter the following formula in 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.
  • $B$19 is the lookup criteria.
  • $B$5:$B$15 is the range for the criteria.
  • ROW(1:1) indicates that the value will be returned vertically.
    • Output: New York
  • Press ENTER.

You will get see the first city in the United States in C19.

  • Drag down the Fill Handle to see the result in the rest of the cells.

Using Fill Handle Tool

This is the output.

Complete City Column

You can apply the formula to other countries:

  • Enter the country name in B19, it will automatically return the cities in the City column.
  • Observe the GIF.

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

To use the INDEX function to match and return multiple values horizontally, use the following formula:

  • Drag the formula to the right to see the City column.

INDEX Function to Match and Return Multiple Values horizontally in Excel


Method 2 – Combining the INDEX and the AGGREGATE Functions to Match and Return Multiple Values Vertically

Steps:

  • Enter the following formula in 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
  • Press ENTER.

You will see the first city in the United States in C18.

  • Drag down the Fill Handle to see the result in the rest of the cells.

Employing Fill Handle Tool

  • This is the output.

The complete City Column


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

Steps:

  • Enter the formula in C18.

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

Inserting TECTJOIN Function

Formula Breakdown

  • B18 is the Criteria.
  • B5:B15 is the Range to match the criteria.
  • C5:C15 is the Range of values.
  • TRUE  ignores all empty cells.
  • TEXTJOIN(“,”,TRUE,IF(B5:B15=B18,C5:C15,””)) → it becomes
    • Output: New York,Chicago,Dallas,Houston
  • Press ENTER.

You can see the result in C18.

Result After Using TEXTJOIN Function


Practice Section

Download the Excel file and practice.

Practice Section


Download Practice Workbook

 


<< Go Back to INDEX MATCH | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo