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]

 

[pbornemeier]

Try this:
=INDEX(B1:B1000,MATCH(MIN(IF(K1:K1000,K1:K1000)),K1:K1000,0))

 

[David1976]

Hmm interesting - it returns the value of 1, on the day with zero stops and on the day showing 2 stops available. It should return a "2", indicating the 2nd of the month.

 

[Kevin@Radstock]

Hi David1976
Try this array formula:
=INDEX($B$2:$B$1000,MATCH(MIN(IF($K$2:$K$1000<>0,$K$2:$K$1000)),$K$2:$K$1000,0))

 

[David1976]

Hi David1976
Try this array formula:
=INDEX($B$2:$B$1000,MATCH(MIN(IF($K$2:$K$1000<>0,$K$2:$K$1000)),$K$2:$K$1000,0))

I appreciate the effort, but it still isn't working. This returns a value of 15 which I can't understand why.

 

[Aladin Akyurek]

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]

Control+shift+enter, not just enter:

=IF(COUNT(K2:K200),INDEX(B2:B100,MATCH(TRUE,ISNUMBER(K2:K200),0)),"Not Available")

 

[Kevin@Radstock]

Hi David

Assuming your data is set up as post #1 and you enter the formula CTRL + SHIFT + ENTER. You should get the result as 2.
Have you a value less then 2 in column K further down!
If I put a 1 in a cell say 5 rows below the 2 and a 15 in column B in the corresponding row. Then the value returns 15.
If your data is in ascending order it should be OK.

Hope that is understandable.

 

[David1976]

Yeah, nothing seems to be working as I am hoping. Maybe I'm coming at it the wrong way. I have thought of another concept. Is it possible to COUNTIF zero, but stop counting as soon as it hits a value greater than zero?
So this would return a value of 3.
[TABLE="width: 104"]
<tbody>[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Aladin Akyurek]

Yeah, nothing seems to be working as I am hoping. Maybe I'm coming at it the wrong way. I have thought of another concept. Is it possible to COUNTIF zero, but stop counting as soon as it hits a value greater than zero?
So this would return a value of 3.
[TABLE="width: 104"]
<tbody>[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

Control+shift+enter, not just enter:

=INDEX(A2:A200,MATCH(1,IF(ISNUMBER(A2:A200),IF(A2:A200>0,1)),0))

 

[David1976]

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