Formula to Look Up & Return Next Available Date After Today

[shmemushi]

Hi

I have a column with availability
Next to it there another column with dates corresponding availability
I need a formula to show me the first date when there is no availability

Attached file below

Thank you

[BanginMyHeadOnMyDesk]

Try this:

Please Login or Register  to view this content.

B.

[Pepe Le Mokko]

Try
=INDEX((INDEX(A2:A100,match(today(),a2:a100,0)):A100),match(0,B2:B100,0))
(untested, no file attached)

[shmemushi]

reply to
=INDEX(A:A,MATCH(0,B:B,0))


That works somehow, but i had to mention that i need to find the first date with 0 availability from today and later.
So if today is 21 aug, that will still show 18 aug, instead of 26 aug. Any other clue? Thank you

[shmemushi]

Try
=INDEX((INDEX(A2:A100,match(today(),a2:a100,0)):A100),match(0,B2:B100,0))
(untested, no file attached)

Thank you but it does not work result
(corrected it will show +5 days)

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Pepe Le Mokko]

Thank you but it does not work result
(corrected it will show +5 days)

Please post a sample sheet ( Go advanced - Manage attachments) so we can work on it. Thanks

[shmemushi]

Update of attachment and title already done, Thank you

[CARACALLA]

My be ?

	D	E	F
14	First 0 Availability	16-Aug	16-Aug
15		21-Aug	26-Aug

F14=IF($E14<>"",MIN(IF($A$2:$A$18>=$E14,IF($B$2:$B$18=$B$5,$A$2:$A$18))),"")


control+shift+enter copy down

[shmemushi]

Thank you Caracalla

According to excel attached
If date today is 18 Aug, I need formula to show me "18 Aug" because today I have 0 availability (not interested any longer on 16 Aug which is 0 availability)
If date today is 21 Aug, i need formula to show me "26 Aug" because on 26 Aug i have 0 availability, and i need that as a kind of warning which later on i will use conditional formula

Thank you

[CARACALLA]

Conditional formatting


=AND($A2=MIN(IF($A$2:$A$18>=$E$2,IF($B$2:$B$18=$B$5,$A$2:$A$18))),$A2<>"")


applies to


=$A$2:$B$100

[shmemushi]

Thanks for your afford, i think i should assume my requests again and re-post it. thank you

[AliGW]

No, pleae do not do that. Any duplicate threads will be closed and you will be sent back here.

Just reformulate your question here in this thread.

[shmemushi]

Reformulated excel sheet attached, please help!
Thanks
Attachment 637883

[CARACALLA]

Invalid Attachment .

[Pepe Le Mokko]

Long winded but seems to be working

Please Login or Register  to view this content.

1. Replace semi colons with commas if need be ( regional settings)
2. Commit with Ctrl-Shift-Enter ( array formula)

[shmemushi]

Valid attached should be on my first post. thank you

[CARACALLA]

Shops

B6=IF(MIN(IF(Shops!$A$2:$A$69=NextSupply!$A6,IF(Shops!$C$2:$C$69=0,IF(Shops!$B$2:$B$69>NextSupply!$A$2,IF(Shops!$B$2:$B$69<>$A$2,Shops!$B$2:$B$69)))))=0,"No supply needed",MIN(IF(Shops!$A$2:$A$69=NextSupply!$A6,IF(Shops!$C$2:$C$69=Shops!$C$4,IF(Shops!$B$2:$B$69>NextSupply!$A$2,IF(Shops!$B$2:$B$69<>$A$2,Shops!$B$2:$B$69))))))

control +shift+enter copy down

changes your file like this