Forum Discussion

Mette820's avatar
Mette820
Copper Contributor
Nov 08, 2019

If or ifs and search function

Hi,    I am trying to do a simple if or ifs statement which includes the search function. I want the search to look at the 1st column and search if it is adsl it is one price and if it is vdsl it i...
excel
PReagan's avatar
PReagan
Bronze Contributor
Nov 08, 2019

Hello Mette820,

 

It appears that you are missing a ")" highlighted in red:

=IF(SEARCH("ADSL",D5),Commercials!$B$6,IF(SEARCH("vdsl",D5),Commercials!$B$7,0))

  • Mette820's avatar
    Mette820
    Copper Contributor
    Nov 08, 2019

    PReaganI get the same result for VDSL which is #value. It seems to be only looking at the first if statement. 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 08, 2019

      Mette820 

      In your formula

      =IF(SEARCH("ADSL",D5), Commercials!$B$6, IF(SEARCH("VDSL",D5), Commercials!$B$7, "no ADSL/VDSL"))

      if you have the text with VDSL the first SEARCH returns an error and entire formula returns error as well. As one of variants you may expand your formula as

      =IF(ISNUMBER(SEARCH("ADSL",D5)), Commercials!$B$6, IF(ISNUMBER(SEARCH("VDSL",D5)), Commercials!$B$7, "no ADSL/VDSL"))
      • Mette820's avatar
        Mette820
        Copper Contributor
        Nov 08, 2019

        SergeiBaklanThanks. So what I want is basically to search if it is adsl it brings back one value, if it is vdsl it brings back another and if it is neither adsl or vdsl it brings back nothing. but it stops at the first if statement so if it is vdsl the whole formula is in error. I want it to bring back a value for vdsl as well.

Resources