formula not working

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: formula not working

  1. #1
    Board Regular
    Join Date
    Mar 2017
    Posts
    96
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default formula not working

     
    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

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,812
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula not working

    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")
    Last edited by barry houdini; Dec 7th, 2017 at 05:28 PM.

  3. #3
    Board Regular
    Join Date
    Mar 2017
    Posts
    96
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula not working

      
    thanks, works perfectly now.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com