Conditional Formatting - Fill cells if field A & B match AND date is <=Today()

I have two excel workbooks that I'm trying to combine. One of them is a "master list" of employees. Contains fairly basic information about employees + some more company-specific data. Looks like this:

Last Name First Name Phone Number SSN Birthdate Normal Payrate Active TWIC Active Basic+ Active Driver's License Legacy Training Active BG Check Active Job # Years Experience Note 1 Note 2 Note 3
Brown Jeff (000) 000-000 123-45-6789 1/11/1911 $10.00 N N N Y N 336 (active job number)


My other workbook is a running spreadsheet of past, current and upcoming jobs and details related to those jobs. Looks like this:


Job # Client Name Facility/Location Man-power Dispatch Date: Hotel/Location VAN ID# Return Date: Additional Notes
336 Client NameHere Panama City, FL 30 11/7/2018 client provided 000000 3/15/2019


I would like to fill/highlight/format Last Name, First Name, Phone Number, SSN & Birthdate based on the following criteria:
IF Job# = [Whiteboard!A$] AND [Whiteboard!ReturnDate] is today or earlier

I can't seem to wrap my head around this seemingly simple function. The system is supposed to work in a way that as my employees type the job number each of our staff gets assigned to, it will flag those employees as being "at work" or "already returned and available". We send crews all over the US for anywhere from 2 days to a year and we're trying to figure out a way to track when they come back so we can send them back out again. I hope I haven't muddled this up too much.

Please attach a sample workbook.
Make sure there is enough data to demonstrate your need but not so much that the file is too big (if you have 10,000 rows of data, you don't need to leave them all in, for example).
Include before and after sheets in the workbook if needed to show the process you're trying to complete or automate. Make sure you include the answers you want to get - put examples in, with notes if necessary to explain details.
Remember to remove any confidential information or, if a cell needs data in it to work, overwrite it with made-up data.
The paperclip icon doesn't work at the moment, so click on Go Advanced under the reply box and then scroll down to Manage Attachments to attach a file.

Thanks for attaching the file.

You can solve this using Index-Match in Conditional Formatting. The formula should look up the job number listed against each person, check the return date of that job number and then check if that date is less than or equal to today.
To do that, follow these steps:
1. On the HBCrewLeaders sheet, select the range you want to Conditionally Format - in your sample file, it's A2:E6.
2. Click Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Enter this formula:

Formula:

Please Login or Register  to view this content.

The first range (in red) is the range on the Whiteboard sheet which contains all the Return Dates.
The second range (in green) is the range on the Whiteboard sheet which contains all the Job Numbers.
The cell in blue is the cell on the HBCrewLeaders sheet which contains the Job Number. Note the $ sign in front of the column letter (L) but not in front of the row number (2).

4. Use the Format button to choose how you want to highlight the Last Name, etc - yellow fill, red text, whatever you want.
5. Click OK, twice.

On your sample file, the result (as of today, 23rd Jan) is that A2:E2 is highlighted because the return date is 3 days ago; the other dates are in the future so those rows aren't highlighted. In the sample file the job number for row 6 doesn't exist on the Whiteboard sheet, but I assume that your real file won't have that problem.

Obviously (I think) you should change the ranges in step 1 and in the step 3 formula to match the data ranges in your real file.
For step 1, either pre-select the range you want the CF to apply to, or set it up for just one row (or a few rows) then change the range to which it applies by changing the Applies to range, by clicking Conditional Formatting → Manage Rules.
There's no problem with making the ranges in the formula bigger than than the data range, to allow for rows to be added but the size of the ranges must be the same; for example using Whiteboard!$J$2:$J$500 and Whiteboard!$A$2:$A$500 is fine, but Whiteboard!$J$2:$J$500 and Whiteboard!$A$2:$A$750 would cause errors.


I hope that works for you. Get back to us if something isn't clear.

You're welcome, glad I could help.

If that takes care of your original question, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.