Conditional formatting dates relative to a name in google sheets
I'm relatively new to using google sheets and formulas and what not.
I am trying to highlight a row of cells using conditional formatting based on whether or not a name and date are both present in another range of cells.
In the case of the names, I need an exact match which I have figured out already easily enough.
In the case of the dates, I need it to highlight only if the leftDate is less than(prior) or equal to the rightDate displayed in another range of cells.
For example, Names and Dates
In the left section of the sheet, the row containing Jacob should be highlighted due to his name being present in the right section of the sheet AND the date being prior.
Where as Nicks row in the left section of the sheet would not be highlighted due to the date being after.
Andrews row on the left would be highlighted due to having the same date.
Mikes row would not due to his name not being present on the right section.
so far for comparing the names I am using a COUNTIF function, and to enable both names and dates to function I have it nested in an AND function, like so
=AND(COUNTIF(leftNames = rightNames),COUNTIF(leftDates>=rightDates in relation to name))
The name portion works it is the date portion I am struggling with, sorry for the long winded explanation. Any help is appreciated!
Thanks
try for range A3:D:
=INDEX(IF($B3<VLOOKUP($A3, $F$3:$G, 2, 0), 1,
MATCH($A3&$B3, $F$3:$F&$G$3:$G, 0)))
