Forum Discussion
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 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:
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
3 Replies
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)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
Thanks! That worked and helped a lot. I tried many approaches and was seriously racking my brain.
