 • 514K Members
• 2,873 Online
• 611K Conversations
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 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:  3 Replies
Solution

# Re: Complex INDEX MATCH formula criteria could be one column or another

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))```

# Re: Complex INDEX MATCH formula criteria could be one column or another

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

# Re: Complex INDEX MATCH formula criteria could be one column or another

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)

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