Forum Discussion
Will a formula work in Conditional Formatting?
I have a spreadsheet with 2 pages. The first tab (master list) lists a person's username and the classes they have to complete. The second tab (completion report) lists usernames, the class ID an...
You can use XLOOKUP to find the row in the completion report that matches the combination of employee username and course ID in the master list, then return the completion status of that row in the completion report (or "No data" if it's not found).
Then test if the returned value is "Successful".
The important thing here is that the lookup_value has a fixed column for column A and a fully relative reference in column B. The lookup_array is the concatenated values for columns A and B of the completion report.
=XLOOKUP(
$A2&B2,
'COMPLETION REPORT'!$A$2:$A$6&'COMPLETION REPORT'!$B$2:$B$6,'COMPLETION REPORT'!$C$2:$C$6,
"No data"
)="Successful"