if statement based on cell range

[RachelMads02]

hello everyone, my name is rachel and i have a question that is hard for me to explain. I've uploaded the workbook for an easier understanding.

This is a trip itinerary and the area of the workbook in question is the F column, specially highlighted in yellow.

Column B is determining whether or not a hotel stay is needed.

Column C is my choice of how long a stay will take place based on whether or not the corresponding cell in B is a "y" value.

Column E is the original departure date or the beginning of this trip.

Column F is the arrival date. Cell F6 is based on Cell E3 & the first time a "y" is present in the corresponding B cell.

Column G is just a duplicate of Column C and may be eliminated in the future.

Column H is simply the Arrival Date Cell + Stay duration (as of now it is the corresponding G cell, but may be switched the corresponding C cell)+1.

What I'm looking for is the next time "y" is present in column B the corresponding F cell needs to display the previous H cell. In my workbook, cell F9 = H6. Cell F10=H9, etc etc.

*I'd also like the cells in the F column to remain blank until a "y" is present in the corresponding B cell.

I hope that makes sense and i hope that it's possible. Any help in this matter would be greatly appreciated. Thank you in advance to anyone who takes time from their day to assist me!!

[Phuocam]

Try this in F7:

=IF(C7="","",LOOKUP(10^10,$H$3:H6))

Copy down.

[RachelMads02]

yes it works, thank you. Could you take a moment and explain to me what the "lookup" function is actually saying?

[JohnTopley]

In F7

LOOKUP(10^10,$H$3:H6))

LOOKUP finds the value in H3:H6 which less than or equal to 10^10: 10^10 is just a very large number so we will always find a number (date as Excel dates are just numbers) less 10^10 AND it will be the last in the range we are looking at.

In F9 (first non-blank cell) it is the date in H6.

in F10 it is F9 .. and so on.

NOTE: for LOOKUP to work, values have to be in ascending sequence (as your dates are)

[RachelMads02]

thank you very much for explaining it, i understand now!

[AliGW]

Hi, Rachel!

LOOKUP(10^10,$H$3:H6)

is tricking Excel into returning the bottom most date in the range $H$3:H6. H6 will increment as you drag copy down, expanding that range one row at a time. 10^10 (ten to the power of ten) is a higher number than will ever be found in the date column (remember that Excel sees dates as serial numbers, so 09/03/2017 is actually 42803), and because it can't find it, it returns the nearest number it can find, which is the latest date in that range.

You can do a similar thing with text entries like this:

LOOKUP("zzzzzzzzz",$H$3:H6)