IF function that tests for any of one of several text strings

[dkjonesau]

Hi all,

I've got a bit of a conundrum.

I have a cell that I want to fetch a value from pasted data, only if an adjacent cell doesn't contain one of a list of possible text strings from the pasted data.
Here's a snapshot.

Currently C12 & D12 fetch their time from R & S if R isn't blank.

I want to add an IF in front of that so that if T contains any of the text strings highlighted in yellow from the =UNIQUE summary of possible values in column T, the time isn't fetched.

For example if T12 contained Recall or Single Day off, despite there being a time in R12 it would not transfer to C12 as would currently occur via the =IF(ISBLANK(R12),"",R12) that currently fetches it.

I know how to use an IF to stop one if one string is present. I was trying to avoid a long and complex nested IF.

Is there a way of adding the strings that are exclusion criteria to a range and then IF searching the range? Or am I stuck with nesting IF's?

Thanks

Dave

 

[RoryA]

Yes, you can put them into one range, then use something like:

=IF(COUNT(SEARCH(some_Range,T11))>0,"",your formula)

 

[dkjonesau]

Yes, you can put them into one range, then use something like:

=IF(COUNT(SEARCH(some_Range,T11))>0,"",your formula)

Hi RoryA,

=SEARCH(some_Range,T11) returns a dynamic range with error values if used on its own.

 

[RoryA]

That’s why I didn’t use it on its own…

 

[dkjonesau]

Hi all,

Went down a rabbit hole last night and got lost in part due to Office 365 on web having limitations I didn't know were there... (added in edit.)

Exploring RoryA's suggestion (& changing it) which resulted in an #VALUE error using

=IF(SUMPRODUCT(SEARCH(Exclude,T11))>0,"Exc",(IF(ISBLANK(R11),"",R11)))

I tried a different direction with

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Exclude,$T12)))>0,"Exc",(IF(ISBLANK(R12),"",R12)))

This is closer to the mark, it will exclude a data inclusion in the time table on the left and manage blank cells, but it is excluding regardless whether the text in column T is in the named range Exclude or not.

Eg Recall is an intended string to be a valid exclusion. Recall in column T will result in "Exc" in the times table on left. But so will Annual Leave in T which is not a term that is even close to anything in the Exclude named range.
(Also worked out setting up named ranges in O365 on the web doesn't work after banging my head on the Mac screen at home last night. Back on the laptop today & working on it.)

Why is the Search in the second formula line above = >0 for Annual in T? When Annual is not in the Exclude named range?

Dave

 

[RoryA]

Is there a reason you’re not using the formula I actually gave you?

 

[dkjonesau]

That’s why I didn’t use it on its own…

I was just breaking it down to find out which parts worked and which did not.

Using it as suggested generates a #VALUE error in my sheet. Any idea why? Pic above. Exploring why taught me a few other things I didn't know I could do.

Thanks for help though. It is appreciated.

Cheers,

Dave

 

[RoryA]

I don’t see where you used my formula. I did not mention sumproduct at any point.

 

[dkjonesau]

Is there a reason you’re not using the formula I actually gave you?

Hi Rory,

Sorry, fixed my original comment this morning. Yours didn't give the #VALUE errror. That was my change. Too many versions in my head.

Yeah there was a reason. If I use yours as supplied it didn't work on the Mac at home last night. I tried named ranges, tried other things. Turned out part of the issue that was a distraction was that Named Ranges isn't fully functional on Web Excel. So I gave up.

Back in the office trying it again now it does the same thing as what my SUMPRODUCT version does. It excludes the data regardless of what is in T. eg. below. Your equation in blue highlight - my ISBLANK at end of it. It's excluding "Recall" which is in the named range Exclude, but also "Annual" which is not. I went looking to try and find something that would look for an exact match but haven't had any luck. If you can see where/why it's picking up the "Annual" (or "Gold" which isn't on the worksheet anywhere) that would fix it.

So long & short of it is that I still don't really have a solution unless there's a way to stop SEARCH picking up strings that are supposed to be valid as well as those that are exclusion criteria.

Cheers

Dave

 

[RoryA]

Does ‘exclude’ include blank cells?