How to determine the smallest index under a special condition?

Question

In the following example:

          A     B
     1          bar
     2    3.0   foo
     3    4.0   foo
------------------------
     4    1.0   bar
------------------------
     5    2.0   bar
     6    5.0   bar 
     7          foo

I would like to get the smallest row index i (that is 4 in our case), for which B(i)="bar" and A(i) is not an empty cell. Could you please suggest a formula/VBA macro for this task?

Answer 1

A nice and simple MINIFS should do what you want.

=MINIFS($A$2:$A$8,$B$2:$B$8,"<>",$C$2:$C$8,"bar")

This just return the minimum index where A is not blank, and B is "bar".

EDIT - Adding an alternative solution using MATCH

Using MATCH is a little more complicated, but works on older versions of Excel and doesn't require the Index column like the previous example does:

=MATCH(1, INDEX(($B$2:$B$8<>"") * ($C$2:$C$8="bar"), 0, 1), 0)