How to apply conditional formatting in Google Sheets using checkboxes from different sheet

Other Questions Google Sheets
1

I’m trying to set up conditional formatting that will color certain cells based on checkbox values from another worksheet.

My setup:

  • Main sheet has names in column A starting from row 7
  • Reference sheet called “Data” has checkboxes in column A and corresponding names in column B

What I want to achieve:
When a checkbox is checked (TRUE) in the Data sheet, I want the matching name on my main sheet to be highlighted with a background color.

My current approach:
I need a formula that does these steps:

  1. First match the name from my main sheet with names in Data!B:B
  2. Then verify if the corresponding checkbox in Data!A:A is TRUE
  3. Apply formatting only when both conditions are met

Current formula attempt:

=AND(NOT(ISBLANK(A7)), COUNTIF(Data!B:B, A7) > 0, VLOOKUP(A7, Data!B:A, 2, FALSE))

The formula isn’t working as expected. I think I might need to use INDIRECT function but I’m not sure how to implement it correctly. Any suggestions on how to fix this conditional formatting rule?

2

easiest fix is swappin your range order. use VLOOKUP(A7, Data!A:B, 2, FALSE) instead. VLOOKUP needs the lookup column first, then the return column. I make this mistake constantly lol. your formula becomes =AND(NOT(ISBLANK(A7)), VLOOKUP(A7, Data!A:B, 2, FALSE)=TRUE) - way simpler than the other solutions.

3

Your original formula breaks because you’re using Data!B:A with VLOOKUP - it can’t handle backwards ranges since it expects the lookup column on the left. Try XLOOKUP if you have it, or just use COUNTIFS which handles this perfectly: =AND(A7<>"", COUNTIFS(Data!B:B, A7, Data!A:A, TRUE)>0) This counts rows where the name matches AND the checkbox is TRUE. Way cleaner than nested lookups. I’ve used COUNTIFS for tons of checkbox conditional formatting - it’s rock solid and handles duplicate names if you run into that later.

4

Your VLOOKUP is broken because you’re trying to return column 2 when column A is actually column 1 in your Data!B:A range. Plus that range is backwards. Had the same issue a few months ago and switched to INDEX/MATCH - works way better.

Try this formula for your conditional formatting:

=AND(NOT(ISBLANK(A7)), ISNUMBER(MATCH(A7, Data!B:B, 0)), INDEX(Data!A:A, MATCH(A7, Data!B:B, 0))=TRUE)

It checks if the cell isn’t blank, uses MATCH to find your name’s position in Data!B:B, then INDEX grabs the checkbox value from Data!A:A at that spot. INDEX/MATCH beats VLOOKUP every time for this stuff, especially with checkbox boolean values.