Forum Discussion

Judith95's avatar
Judith95
Copper Contributor
Apr 30, 2020

Index return multiple value with multiple criteria

Hello,

 

I would need help with a formula I can't make it work and can't find help online.

 

I'm trying to return multiple values with multiple criteria (3). Ideally I would like the data separated in one column but if I can have them even aggregate all together would be great.

 

I attached an example and the formula I tried.

 

If you could help me it will be really great !

 

Thanks,

 

Judith

excel
Formulas and Functions

3 Replies

  • Lewis-H's avatar
    Lewis-H
    Iron Contributor
    Apr 30, 2020
    Step 1: Understanding the foundation.
    Step 2: Insert a normal MATCH INDEX formula.
    Step 3: Change the lookup value to 1.
    Step 4: Enter the criteria.
    Step 5: Ctrl + Shift + Enter.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 30, 2020

    Judith95 

    As variant of above, regular (non-array) formula with dynamic ranges is

    =IFERROR(
   INDEX($D:$D,
      AGGREGATE(15,6,1/
         ($A$1:INDEX(A:A,COUNTA(A:A)+1)=$G$2)/
         ($B$1:INDEX(B:B,COUNTA(A:A)+1)=$H$2)/
         ($C$1:INDEX(C:C,COUNTA(A:A)+1)=$I$2)*
         ROW($A$1:INDEX(A:A,COUNTA(A:A)+1)),
         ROW()-ROW($J$10)
      )
   ),
"")

Resources