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

 

[dkjonesau]

Does ‘exclude’ include blank cells?

It does. The range has been left longer than the current list so that the end user can add other criteria to it.
DJ

 

[RoryA]

That’s the cause of the issue. Why not use a table or dynamic range?

 

[dkjonesau]

Ok. Think I fixed it.

Made the named range Exclude a dynamic range using formula in range definition -

=Sheet1!$AP$11:INDEX(Sheet1!$AP:$AP,MATCH("zzzzzzz",Sheet1!$AP:$AP,1))

Cheers, thanks for that.

DJ

 

[dkjonesau]

Solution was a combination of #2 & #12 thanks to RoryA.

DJ