Searching rows for two entries and returning the successful results (adapting existing code)

Question

I am currently using this code in Excel to find and return, via an array, the results of a search but I need to adapt it to search based on two criteria instead of one and would appreciate some help adjusting the code to do that.

Here's what I have...

=IF(ISERROR(INDEX($B$1:$F$154,SMALL(IF($B$1:$B$154="Good",ROW($B$1:$B$154)),ROW(1:1)),5)),"",INDEX($B$1:$F$154,SMALL(IF($B$1:$B$154="Good",ROW($B$1:$B$154)),ROW(1:1)),5))

It currently searches B1:B154 to look for the entry "Good" and returns the contents of the corresponding cell in column F if it is successful.

What I want to do is search for another term in column A as well as that in column B. In other words, perform two searches. Should it find an entry that is successful based on both criteria, then return the contents of the cell in column F as above.

Any help is much appreciated.

Answer 1

Basing your two-criteria match for progressive (first, second, third, etc matches) results can be made easier with the newer AGGREGATE¹ function.

        

The standard formula in E4 is,

=IFERROR(INDEX(C$1:C$999, AGGREGATE(15, 6, ROW($1:$999)/((A$1:A$999="Bad")*(B$1:B$999="Good")), ROW(1:1))), "")

Fill down as necessary.

---

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}

Answer 2

To add second condition to your search replace:

=IF($B$1:$B$154="Good",ROW($B$1:$B$154)) 

With

=IF(($B$1:$B$154="Good")*($A$1:$A$154="Condition2"),ROW($B$1:$B$154))

This part:

($B$1:$B$154="Good")*($A$1:$A$154="Condition2")

Is an equivalent of:

AND($B$1:$B$154="Good",$A$1:$A$154="Condition2")

But AND often doesn't work well in array formulas (it returns single value).
Another option is to use nested IF statement.

If you are using Excel 2007 or later, you can also simplify your formula and instead of

IF(ISERROR(INDEX(...),"",INDEX(...)))

Use:

IFERROR(INDEX(...),"")