Lookup Next Available Date

[David1976]

Hello,
I'm sure this should be very simple but I can't seem to make it work. I've tried a few vlookups & index/matches but can't get it right.
I need to scan down column K to find the first value greater than zero, then return the value in that row from column B.
When I try vlookup, it seems to only return the value from the top row in column B.
B is Date, K is Available Stops.
[TABLE="width: 964"]
<tbody>[TR]
[TD]Route
[/TD]
[TD]Date
[/TD]
[TD]Day
[/TD]
[TD]Booked.Stops
[/TD]
[TD]Max.Stops
[/TD]
[TD]Booked.Pieces
[/TD]
[TD]Max.Pieces
[/TD]
[TD]Booked.Dollars
[/TD]
[TD]Remaining.Stops
[/TD]
[TD]Remaining.Pieces
[/TD]
[TD]Available.Stops
[/TD]
[/TR]
[TR]
[TD]DACAL
[/TD]
[TD="align: right"]1
[/TD]
[TD]Thu
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]28
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]9161
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]DACAL
[/TD]
[TD="align: right"]2
[/TD]
[TD]Fri
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]120
[/TD]
[TD="align: right"]160
[/TD]
[TD="align: right"]58648
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]DACAL
[/TD]
[TD="align: right"]3
[/TD]
[TD]Sat
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]127
[/TD]
[TD="align: right"]160
[/TD]
[TD="align: right"]45871
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Not quite, Aladin. That returns the value 4, the first value that is greater than zero.
I'm looking to count the number of zeros before the 4.
I have tried this but it doesn't work, it returns a value of 1. =(COUNTIF(K2:K32,">0")>0)+0

Control+shift+enter, not just enter:

=SUM(ISNUMBER(K2:INDEX(K2:K200,MATCH(1,IF(ISNUMBER(K2:K200),IF(K2:K200>0,1)),0)-1))+0)

 

[David1976]

That's got it! Thanks, I'll keep you posted on what I plan to do with this... it's actually quite cool

 

[Aladin Akyurek]

That's got it! Thanks, I'll keep you posted on what I plan to do with this... it's actually quite cool

You are welcome. Thanks for providing feedback.

 

[pbornemeier]

I neglected to specify to confirm with Control+Shift+Enter in post #2 in this thread.

 

[David1976]

I neglected to specify to confirm with Control+Shift+Enter in post #2 in this thread.

Hi Phil, thanks I did try that but it returns a #value!
Without using the array, it returns a 1.