IF function query & strike through command

[eileen123]

Hello, Inquiring if possible to enter a formula that applies to a range of cells if it finds a certain word(s) - to then strike through that word? This is involving two columns of data. i.e. 1st column has a list of Services and the 2nd column has a list of Services that are staying. where would I enter the formula If I want it to find the word "checkin" in both columns (1&2) and then strike through it in the first column only (if possible). This is using Google Sheets, but assuming similar to Excel formula.

Or another way or asking is if I enter the word "checkin" in the 2nd column, can a formula be entered to find "checkin" in the first column and strike through that word?

Hoping that makes sense.
Thanks in advance!

 

[jasonb75]

With excel you would use conditional formatting with a formula to define the rule.

Assuming that list 1 is in A2:A20 and list 2 is in B2:B20 the rule, applied to $A$2:$A$20 would be =MATCH($A2,$B$2:$B$20,0) with strikethrough selected in the font tab of the format options.

For a guide on how to apply this to google sheets, open the first dropdown under 'Advanced Conditional Formatting'

Use conditional formatting rules in Google Sheets - Computer - Google Docs Editors Help

Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number.

support.google.com

 

[eileen123]

Jason, Thanks for that - almost worked. I entered your formula and conditional formatting, except it is putting a strikethrough in both columns A & B for the word checkin. I just want column A to have the strikethrough if the word appears in Column B. Is that possible?

 

[eileen123]

Jason, Thanks for that - almost worked. I entered your formula and conditional formatting, except it is putting a strikethrough in both columns A & B for the word checkin. I just want column A to have the strikethrough if the word appears in Column B. Is that possible?

Jason, Disregard, I figured it out looking at the advanced instruction link you provided. Thank you so much!!!!

 

[eileen123]

One more question, what if I wanted list 2 to apply to a block of cells instead of B2:B20 - it is B2 to I28 - what would that formula look like?

 

[jasonb75]

MATCH only works with a single column (or row), for a block you would need an alternative such as countif.

=COUNTIF($B$2:$I$28,$A2)

Should do it.

 

[eileen123]

You are awesome! Thank you so much!