error with formula

books4cars

Board Regular
Joined
Apr 12, 2007
Messages
200
I have this formula:

=IF(ISNUMBER(SEARCH("LIVE",A2,1)),"LIVE",IF(ISNUMBER(SEARCH("http://www.books4cars,http://books4cars",A2,1)),"B4C",IF(ISNUMBER(SEARCH("Yahoo",A2,1)),"Yahoo",IF(ISNUMBER(SEARCH("Google",A2,1)),"Google",IF(ISNUMBER(SEARCH("AOL",A2,1)),"AOL",IF(ISNUMBER(SEARCH("MSN",A2,1)),"MSN"))))))

When I try to enter it into C2, I get "This formula contains an error..."

When I press OK, it highlights the the word "SEARCH" in the (SEARCH("MSN",A2,1) function. What is wrong here?
 

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
518
It pasted w/o error into my spreadsheet and returned the appropriate search value if I typed that in the search cell or FALSE otherwise. I'm using 2003.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

There's an easier way to write that function: in D1:E6 (or anywhere suitable) type in the following:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>MSN</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>MSN</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AOL</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AOL</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Google</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Google</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Yahoo</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yahoo</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>http://www.books4cars,http://books4cars</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">B4C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>LIVE</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LIVE</TD></TR></TBODY></TABLE>

Then use the following formula:

=LOOKUP(2^15,SEARCH(D1:D6,A2),E1:E6)

which uses the great Barry Houdini's solution to the June/July 2008 Challenge of the Month
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Worked for me too, did you show the whole formula?

Another IF and you'd go beyond the limit of nested functions (7)

You can get round that by using a version like this

=LOOKUP(2^15,SEARCH({"MSN","AOL","Google","Yahoo","http://www.books4cars,http://books4cars","LIVE"},A2),{"MSN","AOL","Google","Yahoo","B4C","LIVE"})

Edit: Richard beat me to it. His version is essentially the same but uses a list on the worksheet rather than an "array constant" to hardcode the list into the formula. Note the items listed in the reverse order to your IF functions to get the same order of precedence....
 
Last edited:

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Worked for me too, did you show the whole formula?

Another IF and you'd reach the limit of nested functions (7)

You can get round that by using a version like this

=LOOKUP(2^15,SEARCH({"MSN","AOL","Google","Yahoo","http://www.books4cars,http://books4cars","LIVE"},A2),{"MSN","AOL","Google","Yahoo","B4C","LIVE"})
Aha! You're getting slow Barry!!!

:LOL:

Happy New Year by the way!
 

books4cars

Board Regular
Joined
Apr 12, 2007
Messages
200
Hi

There's an easier way to write that function: in D1:E6 (or anywhere suitable) type in the following:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>MSN</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>MSN</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AOL</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AOL</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Google</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Google</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Yahoo</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yahoo</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>http://www.books4cars,http://books4cars</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">B4C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>LIVE</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LIVE</TD></TR></TBODY></TABLE>

Then use the following formula:

=LOOKUP(2^15,SEARCH(D1:D6,A2),E1:E6)

which uses the great Barry Houdini's solution to the June/July 2008 Challenge of the Month
Sorry, I'm a newbie. How exactly do I type that in? Can I cut and paste your chart?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
In cell D1 you enter MSN, in cell E1 you also enter MSN, in D2 you enter AOL etc ect. Hopefully the following will make my meaning clearer:

<TABLE style="WIDTH: 247pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=328 border=0 x:str><COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 188pt; mso-width-source: userset; mso-width-alt: 9142" width=250><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 11pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=14 height=17></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 188pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" width=250>D</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" width=64>E</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">MSN</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">MSN</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AOL</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AOL</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>3</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Google</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Google</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yahoo</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yahoo</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">http://www.books4cars,http://books4cars</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">B4C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>6</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LIVE</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LIVE</TD></TR></TBODY></TABLE>

The benefit of doing it like this is that you can easily add additional search terms to the bottom of the list (you need to amend the formula to encompass these in which case).
 

books4cars

Board Regular
Joined
Apr 12, 2007
Messages
200
Worked for me too, did you show the whole formula?

Another IF and you'd go beyond the limit of nested functions (7)

You can get round that by using a version like this

=LOOKUP(2^15,SEARCH({"MSN","AOL","Google","Yahoo","http://www.books4cars,http://books4cars","LIVE"},A2),{"MSN","AOL","Google","Yahoo","B4C","LIVE"})

Edit: Richard beat me to it. His version is essentially the same but uses a list on the worksheet rather than an "array constant" to hardcode the list into the formula. Note the items listed in the reverse order to your IF functions to get the same order of precedence....
This one seems to work the best for me. Here is a sample of my spreadsheet:
webdata.XLS
ABCD
1ReferringPageTotalSource
2http://www.google.ca/search?hl=en&q=helminc&btnG=Google+Search&meta=$21.95Google
3http://au.ask.com/web?q=motor+manuals&qsrc=19&l=dis&o=101598&b=100003&ifr=1$159.95#N/A
4$149.95#N/A
5$89.95#N/A
6http://search.yahoo.com/search;_ylt=AuokX6YzVrRg5NQDYWoOcs2mN3wV?p=automobile+service+manual&fr=att-portal&toggle=1&cop=&ei=UTF-8$122.90Yahoo
7http://search.yahoo.com/search;_ylt=AlM_PGyDzySEm0zZs2yjahqLNmYD?ei=UTF-8&fr=inside-yahoo&p=www.books4cars.com$39.95Yahoo
8http://search.comcast.net/?q=chilton+book+company&cat=Web&con=betaa$29.95#N/A
9http://search.aol.com/aol/search?invocationType=comsearch30&query=helminc.com&do=Search$34.95AOL
10http://shopping.yahoo.com/search;_ylt=Arcj4sDRJo.MqzKXWBasOPobFt0A?p=BMW+Owner%27s+Manual&did=$39.95Yahoo
11http://www.google.com/search?q=jaguar+xk8+owners+manual&rls=com.microsoft:en-us:IE-Address&ie=UTF-8&oe=UTF-8&sourceid=ie7&rlz=1I7GPEA_en$39.95Google
12http://www.google.com/search?sourceid=navclient&aq=t&ie=UTF-8&rlz=1T4SUNA_enUS283US284&q=toyota+MR2+and+owners+and+manual$29.95Google
13http://www.google.com/search?client=safari&rls=en&q=03+saab+repair+manual&ie=UTF-8&oe=UTF-8$39.95Google
14http://www.google.com/search?hl=en&q=www.books4cars.com&btnG=Google+Search&aq=f&oq=$29.95Google
15$39.95#N/A
16http://aolsearch.aol.com/aol/search?encquery=ef96b17238efbb09d10ad8858bd83efaf7c1deeb8e0fdcd7&invocationType=keyword_rollover&ie=UTF-8$89.95AOL
EverestOrderData


How can I make the blank cells in column A have a different value (such as "Blank") in column C instead of #N/A?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You can add another IF to return blank if A2 is blank, i.e.

=IF(A2="","",LOOKUP(2^15,SEARCH({"MSN","AOL","Google","Yahoo","http://www.books4cars,http://books4cars","LIVE"},A2),{"MSN","AOL","Google","Yahoo","B4C","LIVE"}))

but you'd still get some #N/As where there are no matches, e.g. in C3 on your sample. If you want to avoid all #N/As change to

=LOOKUP("zzz",CHOOSE({1,2},"",LOOKUP(2^15,SEARCH({"MSN","AOL","Google","Yahoo","http://www.books4cars,http://books4cars","LIVE"},A2),{"MSN","AOL","Google","Yahoo","B4C","LIVE"})))
 

Forum statistics

Threads
1,085,410
Messages
5,383,492
Members
401,833
Latest member
Davezilla

Some videos you may like

This Week's Hot Topics

Top