Find nearest available date that meets criteria

I will have a cell which will be a date input, i'd like it so that the date entered is then checked against a table and in that table will be a column of dates and in the next column will have cells containing 0 or 1.

I would like the date to match either that requested but if the adjoining cell has a zero, go to the next available date with a 1 beside it. Prefer it if the next date was chosen from dates less than the first date that returned 0 but if none of the earlier dates have a 1 then look at the dates greater than the one input, if none of the dates checked have a 1 then i'd like to return the word "no other dates available". I know the formatting of the cells being a date or text may be an issue, but if i can get the first part right i think i know how to change the return to a text date.

As always, much obliged - not looking for a VBA solution and the end result will be used on Excel 2003 version

scottylad2,

Attached is an example workbook based on the criteria you described.
Row 1 is a header row so actual data starts in row 2
The table is in columns A and B
Column A is a list of dates
Column B is a list of 1's and 0's
In cell E2 is where you can enter the date
In cell E3 is this formula:

Please Login or Register  to view this content.

Is something like that what you're looking for?

tigeravatar that's almost perfect, many thanks, could i ask one more adjustment in that rather than 1for the criteria could it be >=1? i can't quite figure out where i would alter your awesome formula

I figured the alteration out, agai, thanks very much. This forum rocks!

Change every instance of: B2:B32=1
to include the greater than: B2:B32>=1