return previous entry if INDEX returns a blank entry (in a broken series of columns)

**rjmills18** · 09-18-2008, 04:43 AM

hi
i have the same 5 columns repeating in a spreadsheet (for each day of the month) and would like 5 cells returning the results for the current day at the left of the page. this bit i can do, however, if there is no entry for a particular day, i would like the most recent entry returned, i.e. for the city column, which is repeated for each day, if the entry for 18th reads Auckland, then this should be returned, however, if there has been no entry since the 6th, which was Singapore, then this should be returned, only i am not sure how this can be done!
(to expand this further maybe the previous sheet (previous month) could be searched for the most recent entry??? if not, the same sheet will be good enough!)
thanks for any help!

**rwgrietveld** · 09-18-2008, 05:15 AM

This is a long explination and I do not fully understand. please add the worksheet and maybe put in some cells with the end result by hand.

**rjmills18** · 09-18-2008, 05:47 AM

here is the file as is.
if, for example, the day was the 10th, i would like last location cell to lookup the city detailed on the 10th, see there was no entry and return the most recent previous entry, in this case EMA - ROM on the 7th?
any clearer, if not i will try to explain again!
cheers