LOOKUP A VALUE IN A ROW and COUNT Number of BLANKS Below it till NEXT Value

I have a row like below

4
Blank
Blank
Blank
Blank
6
Blank
Blank
10
Blank
Blank
Blank
.
.
.
I want a formula to LOOKup a value and count blanks below (till next value).

Please help

attached is the sample

look up values will be picked from a cell, in this case lets say lookup value is 7 and expected result is "5" (as there are 5 blank cells between 7 and next value.

Try

=SUMPRODUCT(--($D$1:$D$19=INDEX($D$1:$D$19,MATCH($F$1,$A$1:$A$19,0))))

F1= lookup value

thanks, it works....but what if there is no column D?

IT WORKS, BUT VALUES IN coloumn D can be different, so column d should not matter. please modify the formula.

if u just want the count of blank cells in the given range u can use =COUNTBLANK(Range) ,,

if it is something else give one example..

But i dont want in full range, i just want the count of blank between lookup value and next "non-blank" value below in a ROW

please have a look

Are numbers in "A" ALWAYS ascending?

Yes. Always ascending. but rarely there might be a big number in between

Try this BUT assumes ascending numbers in A

=MATCH(SMALL($A$1:$A$97,RANK($E$1,$A$1:$A$97,1)+1),A$1:A$97,0)-MATCH($E$1,$A$1:$A$97,0)-1

Works!! but returns the error if there are variation in B and C Columns are sometimes there may be blank cells in B and C Coloums

As formula doesn't use B or C I don't see why it doesn't work. I cleared B & C and it still gave the correct answer.

I don't why are doing this anyway: I can't see any logic for it!

thanks a lot john, i misinterpreted it, your formula works. my problem is resolved. thank you again :-). just in case would be great if you can resolve ascending limitation.

thanks nflsales, would be great if you can modify the formula which is no relation to the values in B,C,D column (as there might be variations), cant we just use A Column?

we can ignore last number sir.

Works like a charm! Thank you.