OR or Wildcard within ISNUMBER SEARCH

[dslhs]

Hi,

I have this formula:

=IFERROR(INDEX('Progress Data'!A2:A100, SMALL(IF(ISNUMBER(SEARCH("Below",'Progress Data'!G2:G100)), MATCH(ROW('Progress Data'!G2:G100), ROW('Progress Data'!G2:G100)), ""), ROWS('Progress Data'!$A$1:A1))), "")

Which works perfectly, except I want to make it so the array returns based on cells that say either 'Below Expectation' or 'Significantly Below Expectation'. Currently it only returns based on cells that return 'Below Expectation'

I've tried a wildcard for Below and including Or in the formula, but to no avail. I'm pretty sure someone way better at this than me will see where I've gone wrong in 2 second. Any ideas?

Many thanks,

 

[arthurbr]

SEARCH does not need wildcards. It will return a number wherever the search string is found if any.
In your case it will find "below" in both strings and return the position, otherwise an error

 

[dslhs]

Then why is the formula returning results for 'Below Expectation' but not 'Significantly Below Expectation? How can I make it return both?

 

[arthurbr]

Without seeing your actual data I don't know. Could you post a small example using the XL2BB addin on this site?

 

[Peter_SSs]

Could it be that you have not locked the rows numbers so that when you drag the formula down you are no longer looking at the same ranges.

=IFERROR(INDEX('Progress Data'!A$2:A$100, SMALL(IF(ISNUMBER(SEARCH("Below",'Progress Data'!G$2:G$100)), MATCH(ROW('Progress Data'!G$2:G$100), ROW('Progress Data'!G$2:G$100)), ""), ROWS('Progress Data'!$A$1:A1))), "")

Note that you could also use this slightly shorter one.

=IFERROR(INDEX('Progress Data'!A:A,AGGREGATE(15,6,ROW('Progress Data'!A$2:A$100)/ISNUMBER(SEARCH("Below",'Progress Data'!G$2:G$100)),ROWS('Progress Data'!A$1:A1))),"")

Both of the above work for me.

BTW, what is the name of the worksheet that this formula is on?

 

[dslhs]

Could it be that you have not locked the rows numbers so that when you drag the formula down you are no longer looking at the same ranges.

=IFERROR(INDEX('Progress Data'!A$2:A$100, SMALL(IF(ISNUMBER(SEARCH("Below",'Progress Data'!G$2:G$100)), MATCH(ROW('Progress Data'!G$2:G$100), ROW('Progress Data'!G$2:G$100)), ""), ROWS('Progress Data'!$A$1:A1))), "")

Note that you could also use this slightly shorter one.

=IFERROR(INDEX('Progress Data'!A:A,AGGREGATE(15,6,ROW('Progress Data'!A$2:A$100)/ISNUMBER(SEARCH("Below",'Progress Data'!G$2:G$100)),ROWS('Progress Data'!A$1:A1))),"")

Both of the above work for me.

BTW, what is the name of the worksheet that this formula is on?

This is perfect. It was so simple. Thank you so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.