Indirect function in Index/Match Array

[hoosier41]

I have the following formula working correctly, but I also need to find the
next instances of the occurance and I'm not sure where the INDIRECT part of
the formula should go. I couldn't tell from the other posts similar to this
what I need to do in my particular situation. Can anyone help? Thanks!

=INDEX('Master Com Plan'!G15:G44,MATCH(1,('Master Com Plan'!A15:A44="SDS
PM")*('Master Com Plan'!H15:H44="SDS Leads")*('Master Com Plan'!Q15:Q44=7),0))

 

[Bob Phillips]

Try this array formula

=IF(ISERROR(SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS
Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($A1))),"",
INDEX($G$15:$G$44,SMALL(IF(($A$15:$A$44="SDS PM")*($H$15:$H$44="SDS
Leads")*($Q$15:$Q$44=7),ROW($A$15:$A$44),""),ROW($A1))-ROW($G$15)+1))

 

[hoosier41]

Thanks Bob, I think that starts me on the right path, but it's not returning
any value. I'm not getting an error or anything, just a blank cell. I'm
wondering about the row references to $A1. My A1 has the title of the
worksheet, but no values. Do I need something different here for the formula
to work?

 

[Bob Phillips]

The A1 is just a row number, it is referring explicitly to the cell data.

Did you array enter it?

 

[hoosier41]

Yes, I entered it as an array and I should have two more instances that match
the criteria. It's not giving me an error like you get if you don't enter
the array.

I'm checking this criteria and pulling it from another worksheet in the same
workbook, do I need to reference that sheet name instead of just the cell
number, like $A$15? Thanks for all your help!

 

[Bob Phillips]

You would need to use the sheet name wherever applicable yes. Either the
INDEX, or the conditions.