Forum Discussion
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 is another price. It works for adsl but not for vdsl. I have tried with ifs but it brings back the same
My formula so far: =IF(SEARCH("ADSL",D5),Commercials!$B$6,IF(SEARCH("vdsl",D5,Commercials!$B$7),0))
ADSL 5/0.5 145.00
ADSL 7/0.5 145.00
VDSL 50/10 #VALUE!
13 Replies
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"))
- Try this:
=IFERROR(CHOOSE(
MATCH(D5,
{“adsl”,”vdsl”},0),
Commercials!B$6,Commercials!B$7),
0)- I inadvertently misconstrued the requirement. Thus, try this instead:
=IF(COUNTIF(D5,”*adsl*”),
Commercials!B$6,
IF(COUNTIF(D5,”*vdsl*”),
Commercials!B$7,
0))
The foregoing formula also considers the possibility that certain characters may precede or follow the search texts. The use of COUNTIF with wildcards is a shorter alternative to the ISNUMBER-SEARCH combination.
- WOuldn't it be better to use VLOOKUP to look up ADSL or VDSL from a list?
JKPieterseThanks but I have 1 ADSL value which needs to be used for any kind of ADSL xxx (it doesn't matter what comes ADSL) type hence the search function. Same for VDSL.
