Complex INDEX MATCH formula criteria could be one column or another

Occasional 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:



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:



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



For such data


that could be

  INDEX( ($A$2:$A$30="Diamond")*($G$2:$G$30="Next")*(

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: 


Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies