formula not working

[Godders199]

Hello,, I am trying to use the following formula to determine whether within cell L2, one of the products detailed is a investment, the cell contains various products seperated by comas, for example bb111, rc3333, fd4444 , some products do have a gap eg xxx xxx2

on the list tab column b contains a list of all the investment codes

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Lists!$B$2:$B$14,'cases available '!$L2))),"investment","no")

Currently this formula will return "investment" no matter what is in the cell, it will only return "no" if the cell is blank.

I have also tried the forumal without the sumproduct, but that makes no difference.
I have converted the worksheet from table format, but i dont believe that is the issue, i have checked my list and that all seems in order.

anyone got any ideas?

Thanks

 

[barry houdini]

That formula looks OK to me but you can't have any blanks in Lists!$B$2:$B$14

If you want to allow blanks in that range then change the formula to this:

=IF(SUMPRODUCT(( Lists!$B$2:$B$14<>"")*ISNUMBER(SEARCH(Lists!$B$2:$B$14,'cases available '!$L2))),"investment","no")
​

 

[Godders199]

thanks, works perfectly now.