Got it to work for the sample given, but it is NOT correct - it's using the
value of the max/min found instead of the index to get the relative position to grab the date from the header.
Anybody have an idea as to how to resolve that?
A | B | 19/2/2019 | D | E | F | G | H | I | J | K | L | M | N |
| | | | | | | | Fri | Mon | Tue | Wed | Thu | Fri |
ID | Name | Data | Data | Data | Last Visit | Next Booking | 15/2/2019 | 18/2/2019 | 19/2/2019 | 20/2/2019 | 21/2/2019 | 22/2/2019 | |
1 | Anna | x | x | x | 18/2/2019 | 21/2/2019 | | 8:30 | 9:00 | | | 12:00 | |
2 | Brian | x | x | x | NEW | 20/2/2019 | | | | | 10:00 | | |
3 | Charlie | x | x | x | 15/2/2019 | 22/2/2019 | | 11:00 | | | | | 15:00 |
4 | Donna | x | x | x | 18/2/2019 | NO BOOKING | | 12:00 | | | | | |
5 | Eva | x | x | x | 15/2/2019 | 20/2/2019 | | 8:30 | | | 15:30 | | |
<tbody>
</tbody>
Note: This will consistently return the time value for the client's row:
=MAXIFS(I4:KU4,$I$3:$KU$3,"<="&TODAY())
But you need the header date. The below will find the header for the value found, which works for the above, but if you change 8:30 in I4 to 9:00, the date in F2 will change to 15/2/2019.
This was used in F4 and copied down:
=IFERROR(INDEX($I$3:$KU$3,0,MATCH(
MAXIFS(I4:KU4,$I$3:$KU$3,"<="&TODAY()),I4:KU4,0)),"NEW")
This is in G4
=IFERROR(INDEX($I$3:$KU$3,0,MATCH(MINIFS(I4:KU4,$I$3:$KU$3,">"&TODAY()),I4:KU4,0)),"NO BOOKING")