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
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