Hi, here is another option to try - these are array functions and need to be entered using CTRL+SHIFT+ENTER.
Excel 2013/2016
| A | B | C | D | E | F | G | H | I | J | K | L | M |
---|
| | | | | | | | | | | | | |
Fri | Mon | Tue | Wed | Thu | Fri | | | | | | | | |
ID | Name | Data | Data | Data | Last Visit | Next Booking | | | | | | | |
Anna | x | x | x | | | | | | | | | | |
Brian | x | x | x | NEW | | | | | | | | | |
Charlie | x | x | x | | | | | | | | | | |
Donna | x | x | x | NO BOOKING | | | | | | | | | |
Eva | x | x | x | | | | | | | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]15/02/2019[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD="align: right"]21/02/2019[/TD]
[TD="align: right"]22/02/2019[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]18/02/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]21/02/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15/02/2019[/TD]
[TD="align: right"]22/02/2019[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15:00[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15/02/2019[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15:30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
</tbody>
Sheet1
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]{=IFERROR(
LOOKUP(2,IF($H$3:$M$3<=$C$1,IF(ISNUMBER(H4:M4),1)),$H$3:$M$3),"NEW")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]{=IFERROR(
INDEX($H$3:$M$3,MATCH(1,IF($H$3:$M$3>$C$1,IF(ISNUMBER(H4:M4),1)),0)),"NO BOOKING")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]