I have a spreadsheet thats setup as a tasking calendar. The dates are in the heading row and the users names are down the side. When a user has a tasking, the date range in their row is merged and labeled with the task and date.
I'm attempting to make a cell in their row that when you input a certian date into another random cell, it pulls up the tasking the user is assigned to for that day. I'm using the HLOOKUP command to reference the date value and find the needed cell. If the data is directly typed into the 1st left-most cell, the information is presented just as planned. If the date referenced is in the middle of the merged cells, nothing is presented.
Example:
Cells A1:C1 are merged with the text "SAMPLE DATA" in it.
If the HLOOKUP command references cell A1, it comes back with SAMPLE DATA
If the HLOOKUP command references cells B1 or C1, it comes back with 0
In this example, I need the HLOOKUP command to give me "SAMPLE DATA" when any of the 3 cells are referenced. I've seen solutions with INDEX/MATCH formulas but these merged cells are all different depending on the date ranges so a dynamic solution is in order.
I understand this is the nature of the beast so I'm wondering if theres a way to dynamically find and reference the first cell of the merged cell or maybe a fancy formatting job may be in order. Any help would be much appreciated.
Bookmarks