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?
CodePudding user response:
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)