Formula help

[markster]

I need to get up to speed with formulas, but I'm battling to meet a deadline to draw out key info on a 30,000 row spreadheet.

Basically I currently have a formula in U2 as follows: =IF(E2="","In Scope","Out of Scope")

I want to expand it do do other things but have no idea where to start.

So basically , I want it to look at E2 first and if it is blank, return "In Scope", except for where cell T2 contains any words listed in range Z2 to Z25 (basically this is a separate table that has words like Teaching, Tutor, Transition Payment, Overtime, Holiday Pay, Academic, Research (list is growing by the hour) if any of the words are present it returns 'Out of Scope'

Can anyone help?

Thanks very much.
M

 

[Aladin Akyurek]

Looks like:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($Z$2:$Z$25,T2))),"out of scope",IF(E2="","in scope","out of scope"))

 

[markster]

Looks like:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($Z$2:$Z$25,T2))),"out of scope",IF(E2="","in scope","out of scope"))

Hi mate

Just tried this and it seems to return 'out of scope' for all regardless of what's in any of the cells - am I doing something wrong?

Thanks
Mark

 

[Aladin Akyurek]

Hi mate

Just tried this and it seems to return 'out of scope' for all regardless of what's in any of the cells - am I doing something wrong?

Thanks
Mark

Does this better?

=IF(COUNTIFS($Z$2:$Z$25,T2),"out of scope",IF(E2="","in scope","out of scope"))

 

[markster]

Does this better?


Hi mate it's almost there the problem is that there are several words in T2 so this is the content of T2:

F7:Graduate Skills Course 2017-18

So the word "Graduate" is on my list of excluded words as T2 contains Graduate. If I delete the contents of the cell and type in Graduate the formula works but it doesn't work when there are other words present too. Can this be fixed? as i'm working with some very poor data.

Thanks so much.
Mark

 

[Aladin Akyurek]

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&$Z$2:$Z$25&" "," "&T2&" "))),"out of scope",IF(E2="","in scope","out of scope"))

provided that $Z$2:$Z$25 contains no empty cell(s).

 

[markster]

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&$Z$2:$Z$25&" "," "&T2&" "))),"out of scope",IF(E2="","in scope","out of scope"))

provided that $Z$2:$Z$25 contains no empty cell(s).

Works perfectly thank you.
Have a great weekend.
Mark

 

[Aladin Akyurek]

Works perfectly thank you.

You are welcome.

Have a great weekend.
Mark

You too.