=IFERROR(IF(DATEDIF(E$1,FILTER('R1'!$A$2:$A,'R1'!$C$2:$C = $B2, VALUE('R1'!$A$2:$A) >= VALUE(E$1)),"D") = 0, "x",""))
There's a lot going on here, hopefully this explanation will suffice.
Firstly, we FILTER
the R1 Sheet's entire A column, save for the header. We then have two conditions that must be met: The C column must be equal to the B2 (i.e. specifying the last name) and that the value of the A column must be greater than or equal to the value found in the first row (i.e. checking against the date). I had to wrap the date in a VALUE
formula since it wasn't comparing correctly without it.
This leaves us with all the rows with the correct person. Notice that we are using >=
instead of a simple equal sign, but this is necessary since the date value in the A column has a time component which makes gives them a greater value.
We then input that array we got from filtering into the second argument of DATEDIF
or date difference. Using an array this way means the first value in the array will be considered. The first argument is the date we are looking at. It's in this order because the function returns an error if it's the other way around. Setting the third argument as 'D' tell the function to return how many days there are in between the two days.
With the difference, check if the value is 0, if it is return a x, and if there isn't then don't return anything.
Wrap the entire thing in IFERROR
since FILTER
returns an error if it didn't find anything that matches the search.
NOTE: this probably isn't the most efficient way of doing this since each cell has to have the formula above. Set it at the top most corner and then drag it to ensure that the cell references are correct.