Return first visible row number that contains specific value

[Worker8ee]

I cannot figure out if there is a formula that would enable me to return the row number of the first visible row that contains a specific value. Let's say I have varying words in range A2:A100, I want to return the first visible row that contains the word "Apple", keep in mind that other rows have been hidden using a filter and that some of those might have the word "Apple" as well. Any ideas are appreciated.

 

[DanteAmor]

Try this array formula:

varios 07jul2020.xlsm
	A	B
1	A	9
5	Orange
9	Apple
13	Banana
17	Apple
18
19
20
Hoja11

Cell Formulas
Range	Formula
B1	B1	=MIN(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1))>0,IF(A2:A20="Apple",ROW(A2:A20))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Worker8ee]

Thanks Dante that did the trick! I am extremely grateful for you taking the time to help me with this. In case other users use this solution I do want to point out that I changed one part of your formula to be 'subtotal(103' instead of 'subtotal(3' so that it would ignore the hidden rows. Thank you again!

Try this array formula:

varios 07jul2020.xlsm
	A	B
1	A	9
5	Orange
9	Apple
13	Banana
17	Apple
18
19
20
Hoja11

Cell Formulas
Range	Formula
B1	B1	=MIN(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1))>0,IF(A2:A20="Apple",ROW(A2:A20))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.