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.

Route	Date	Day	Booked.Stops	Max.Stops	Booked.Pieces	Max.Pieces	Booked.Dollars	Remaining.Stops	Remaining.Pieces	Available.Stops
DACAL	1	Thu	8	8	28	40	9161		12
DACAL	2	Fri	32	34	120	160	58648	2	40	2
DACAL	3	Sat	34	34	127	160	45871		33

<tbody>
</tbody>

 

[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.

Route	Date	Day	Booked.Stops	Max.Stops	Booked.Pieces	Max.Pieces	Booked.Dollars	Remaining.Stops	Remaining.Pieces	Available.Stops
DACAL	1	Thu	8	8	28	40	9161		12
DACAL	2	Fri	32	34	120	160	58648	2	40	2
DACAL	3	Sat	34	34	127	160	45871		33

<tbody>
</tbody>

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.

0
0
0
4
0
0
6
6

<tbody>
</tbody><colgroup><col></colgroup>

 

[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.

0
0
0
4
0
0
6
6

<tbody>
</tbody>

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