Forum Discussion

jkoors's avatar
jkoors
Copper Contributor
Jan 29, 2019
Solved

Complex INDEX MATCH formula criteria could be one column or another

I am trying to query a sheet called DataImportTable using INDEX MATCH combo, the only challenge I have is that is I'm looking to match multiple criteria, however, I need to get a specific label in th...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 29, 2019

    Hi,

     

    For such data

    that could be

    =INDEX($C$2:$C$30,MATCH(1,
  INDEX( ($A$2:$A$30="Diamond")*($G$2:$G$30="Next")*(
   ($I$2:$I$30="TeamA")+($J$2:$J$30="TeamA")+($K$2:$K$30="TeamA")),
  0),
0))

    Please see attached

Twifoo's avatar
Twifoo
Silver Contributor
Feb 08, 2019

The formula can be simplified through the LOOKUP function, as follows: 

=LOOKUP(2,1/(($A$2:$A$30="Diamond")*($G$2:$G$30="Next")*
((($I$2:$I$30="TeamA")+($J$2:$J$30="TeamA")+($K$2:$K$30="TeamA"))>0)),
$C$2:$C$30)

Resources