Help with a Formula (contains rage or hard coded text)

[sakrams]

Hi Excel Gurus,

I managed to achieve what I was after, but want to show "Internal" if the statement is False and "External" If True. Currently the first two formulas result in True or False only.

=SUMPRODUCT(--ISNUMBER(SEARCH({"STL","SCL","STM","STS"},C2)))>0


=SUMPRODUCT(--ISNUMBER(SEARCH($Q$14:$Q$17,C2)))>0

I was able to do it using the following formula but I can't use the range or hard coded values

=IF(ISNUMBER(SEARCH("STL",C2)), "External","Internal")

Thanks for your help in advance.

 

[Aladin Akyurek]

Either...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"STL","SCL","STM","STS"},C2))),"Internal","External")

Or...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"STL","SCL","STM","STS"},C2))),"Internal",IF(C2="","","External"))

The latter also tests whether C2 is blank.

 

[sakrams]

Thank you.. Thank you.. Thank you...

Either...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"STL","SCL","STM","STS"},C2))),"Internal","External")

Or...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"STL","SCL","STM","STS"},C2))),"Internal",IF(C2="","","External"))

The latter also tests whether C2 is blank.

 

[Aladin Akyurek]

Thank you.. Thank you.. Thank you...

Most welcome.