Return next date if....

[Steves73]

Hi All,

I know there is a way just can't think of the formula

As shown below I have formulas that returns a date in Cell F12 if sees the first 250 in row 11. As 250 comes up often I need to capture in cell G12 the next date (In this case it would be 14-Aug-23) after cell F12 is populated

I.E, Formula looks at F12, see it has a date in it, so returns the date of when the next 250 come along. in this case it should return 14-Aug-23

Hope this makes sense

 

[Anthony47]

And which is your Office version?
And 14-Aug-23 is the correct answer for you because it is "the second" occourrence for 250 or because it is "the last" one? Or why?

 

[Steves73]

Office 365
The current formula in Cell F12 picks up the first "250", in need a formula that search's row 11 and picks up the 2nd "250"

 

[Fluff]

Office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Steves73]

Account Edited and updated

 

[Anthony47]

Try, in F12

=IFERROR(LET(yData,$AH$12:$KV$12,yVal,E12,tCol,SMALL(IF(yData=E12,COLUMN(yData),""),2),INDEX($A$10:$KV$10,tCol)),"")

 

[Steves73]

Thanks Mate, You have done it. Works perfectly

Thank you

 

[Fluff]

Account Edited and updated

Thanks for that
Another option in F12 will return the first two dates

=TAKE(FILTER($K$10:$KV$10,$K12:$KV12=$E12),,2)

 

[Steves73]

Just tried that one, impressive, works perfectly as well.

To all, Thank you very much.

 

[Fluff]

Glad we could help & thanks for the feedback.