I need help building a formula in a RefCell column to find the cell under a column where TODAY() <= the date in column heading.
My worksheet looks like this:
![]()
Please Login or Register to view this content.
EXAMPLES: If TODAY() is in the range of:
2/9/2015 - 2/11/2015, then RefCell should be F2 and F3, representing Daisy and Curly.
2/12/2015 - 2/15/2015, then RefCell should be F2 and G3, representing Daisy and Moe.
The date headers represent the date an on-call person changes. Typically, it's a full week, but if someone takes over mid-week, we will insert a new column with the date it occurs (2/12/2015) and only put the name of the exception.
I want a formula that will use the current date to return the cell reference that shows who should be on-call. If the cell is blank, then the formula should look to the left to find the first non-blank cell. I want the reference and not the cell value so I can use the reference in other formulas to return other information about the cell.
Person column will use the RefCell in =INDIRECT(F2) to get the name of the person on-call.
Phone column, and others, will use VLOOKUP to return information from another tab.
This is what I have now (Note the -1 on the second line to go back one column).
It works if the date is 2/12, but not if it's from 2/13-2/15.
=IF(HLOOKUP(LOOKUP(IF(C$2="",TODAY(),C$2),E$1:H$1),E:H,ROW(),FALSE)="",
HLOOKUP(LOOKUP(IF(C$2="",TODAY(),C$2)-1,E$1:H$1),E:H,ROW(),FALSE),
HLOOKUP(LOOKUP(IF(C$2="",TODAY(),C$2),E$1:H$1),E:H,ROW(),FALSE)
)
I assume I might need an array formula, but don't know how to construct one to solve my problem.
Bookmarks