IF function query & strike through command

eileen123

New Member
Joined
Jan 23, 2020
Messages
7
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
7,343
Office Version
2019
Platform
Windows
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'
 

eileen123

New Member
Joined
Jan 23, 2020
Messages
7
Office Version
2016
Platform
Windows
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

New Member
Joined
Jan 23, 2020
Messages
7
Office Version
2016
Platform
Windows
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

New Member
Joined
Jan 23, 2020
Messages
7
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
7,343
Office Version
2019
Platform
Windows
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.
 

Forum statistics

Threads
1,085,151
Messages
5,382,002
Members
401,765
Latest member
Parrotdise

Some videos you may like

This Week's Hot Topics

Top