SOLVED

Complex INDEX MATCH formula criteria could be one column or another

Copper Contributor

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 this row match and this label can be in one of any 3 columns I through K.  

 

The criteria I need to match are:

  • "Diamond" in A:A
  • "Next" in G:G
  • "TeamA" in either column I:I or could be in J:J

This DataImportTable will have a query and updated daily via copy and paste.

 

Here is the formula I'm trying, but isn't producing the results I need:

=INDEX(DataImportTable!$A$2:$K$998,MATCH(1,(DataImportTable!$G$2:$G$998="Next")*(COUNTIF(DataImportTable!$I$2:$I$998,"TeamA")+COUNTIF(DataImportTable!$J$2:$J$998,"TeamA"))*(DataImportTable!$A$2:$A$998="Diamond"),0),2)

 

This formula will work, but I can't always be sure if these labels will be in column I, they may be in column J or column K:

=INDEX(DataImportTable!$A$2:$K$998,MATCH(1,(DataImportTable!$G$2:$G$998="Next")*(DataImportTable!$I$2:$I$998="TeamA")*(DataImportTable!$A$2:$A$998="Diamond"),0),2)

 

If this worked as I need it to, it would return:

BE-160 from row 8 of DataImportTable! in cell 'Status Priority'!$A$5

 

See images:

 

Screen Shot 2019-01-28 at 4.32.33 PM.pngScreen Shot 2019-01-28 at 8.41.43 PM.png

3 Replies
best response confirmed by jkoors (Copper Contributor)
Solution

Hi,

 

For such data

image.png

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

Thanks!  That worked and helped a lot.  I tried many approaches and was seriously racking my brain.

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)

1 best response

Accepted Solutions
best response confirmed by jkoors (Copper Contributor)
Solution

Hi,

 

For such data

image.png

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

View solution in original post