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
    244
    Post Thanks / Like
    Mentioned
    1 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,825
    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
    244
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula not working

    thanks, works perfectly now.

Some videos you may like

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
  •