Excel Tips – A Matching Truth

A user recently asked:

“I have scheduled fixed days throughout the year for a certain task. Some weeks, it isn’t needed at all, and some weeks contain more than one day for this task. It’s also on different days of the week. Instead of scanning a long list of dates each time, how can I devote only a couple of cells in a worksheet to show the next available free appointment?”

Well, here goes – here are the demo output cells in Excel:

Below is (part of) the named range “Appts2013” of available dates in worksheet appointments:

Formula in “Today is” cell is: =TODAY()

The formula in yellow cell with 04-Jul in is: =INDEX(Appts2013,MATCH(TRUE,Appts2013>$B$1,0))

it’s an array formula, so it won’t work unless it is entered properly – once the formula is typed in, with the cell selected,

• Press F2.
• Press the following simultaneously: SHIFT-CTRL-Enter
• That will bring the array formula to life, and add leading and trailing curly braces.

Just in case the user wants to check that long list of dates, Conditional Formatting is used to highlight the date in the list. Formatting is applied against the yellow cell in sheet Next avail being equal to a cell within the dates list: =A$1=’Next avail’!$B$2