CODEWITHSUNDEEP
google-sheets
Bailey Singer
Bailey Singer

Reputation: 45

Google Sheets: Finding a partial match

Right now I have the following in for conditional formatting: =if(I43="Trait Dependency", False, IF(I43="None", False, if(isna(match(I43,B23:B40,0)),True, False)))

When its checking I43's value for a match in B23:B40 is there a way to check it for a partial match?

So lets say I43 = "Thing One or Thing Two" I want the sheet to check if any value in B23:B40 contains "Thing One" or "Thing Two" that way the formatting will return True if "Thing One" or "Thing Two" is present, but return false under other conditions

Current Sheet: https://docs.google.com/spreadsheets/d/1-nR2J9a82ZDpOhWi0vee7LRxP03L96DYnML4Lawyw3g/edit#gid=1321754661

I took a few steps to simplify things for my benefit, but I'm still running into a wall. Right now, if I43 does not match any values in B23:B40 it changes the formatting as it should. I need it to check the value of I43 and J43 for formatting changes. Taking it a step further ideally it'll function as follows:

If I43 OR J43 do not match any values in B23:40, turn red.
If I43 AND J43 do not BOTH match any values in B23:B40 turn red.
Otherwise stay white.

I was just fiddling with the either/or part (I43 OR J43) and came up with
=if(I43="Trait Dependency", False, IF(I43="None", False, if(isna(match(I43,B23:B40,0)),True, if(isna(match(J43,B23:B40,0)),True,False))))
But that doesn't do what I need it to do. It seems to stop right after if(isna(match(I43,B23:B40,0)),True and doesn't do the last check

Upvotes: 0

Views: 68

Answers (1)

player0
player0

Reputation: 1

try:

=IF(I43="Trait Dependency", FALSE, IF(I43="None", FALSE, 
 IF((ISNA(MATCH("Thing One", B23:B40, 0)))+
    (ISNA(MATCH("Thing Two", B23:B40, 0))), TRUE, FALSE)))

or:

=IF(I43="Trait Dependency", FALSE, IF(I43="None", FALSE, 
 IF(ISNA(MATCH("*"&I43&"*", B23:B40, 0)), TRUE, FALSE)))

Upvotes: 2

Related Questions