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
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies
Need FORMULA help please!!
marislav in Excel on
4 Replies