So we've got a calendar on google sheets, it has a bunch of different things besides just the days but the part I'm working on is easily displaying who is not going to be working that day.

We have a list of people who are being scheduled for things during the day, and they are in different places in a range almost every day so this solution needs to be flexible enough to deal with that.

What I am trying to accomplish is to be able to input a name and reason in the section at the bottom, and to have the actual schedule part automatically strikethrough any name that has been input into the bottom section.

I've made an example Sheet where you can see the input range is C116:J125 and the range I would like names to be struckthrough is D4:D73.

I've searched as much as I can but I'm either not experienced enough or simply cannot find a solution for this. I've found many answer to things that are almost fixes, but as you all probably know Sheets is rather specific and none of those answer have actually worked. Thanks in advance!

1 Answer 1


@Steven, I've added a sheet ("Erik Help") with the following custom CF formula/rule applied to D4:D73 ...



NOT(ISERROR(VLOOKUP(...))) basically means that something was found (i.e., if it's not an error when Sheets tries to find it, then it was found).

The "*" appended to the front and back of D4 are wildcards. This means "whatever is in D4, even if there is other stuff before it or after it."

Between the curly brackets {...}, I've just stacked your two bottom ranges where you list people who are out. That's the range that the VLOOKUP will search.

...1, FALSE) means "search column 1" (because, when stacked, there is only one virtual column in memory) and that the items in that column are in no particular order (i.e., ordered data = FALSE).

  • Perfect solution thank you so much Erik!
    – Steven
    Nov 11, 2020 at 18:25
  • My pleasure, Steven.
    – Erik Tyler
    Nov 12, 2020 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.