error with formula

[books4cars]

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]

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]

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]

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

 

[Richard Schollar]

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



Happy New Year by the way!

 

[barry houdini]

Aha! You're getting slow Barry!!!

I know......it was typing out that whole list.....should have done it like you did!

Happy New Year to you too, Richard

 

[books4cars]

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]

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]

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
	A	B	C	D
1	ReferringPage	Total	Source
2	http://www.google.ca/search?hl=en&q=helminc&btnG=Google+Search&meta=	$21.95	Google
3	http://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
6	http://search.yahoo.com/search;_ylt=AuokX6YzVrRg5NQDYWoOcs2mN3wV?p=automobile+service+manual&fr=att-portal&toggle=1&cop=&ei=UTF-8	$122.90	Yahoo
7	http://search.yahoo.com/search;_ylt=AlM_PGyDzySEm0zZs2yjahqLNmYD?ei=UTF-8&fr=inside-yahoo&p=www.books4cars.com	$39.95	Yahoo
8	http://search.comcast.net/?q=chilton+book+company&cat=Web&con=betaa	$29.95	#N/A
9	http://search.aol.com/aol/search?invocationType=comsearch30&query=helminc.com&do=Search	$34.95	AOL
10	http://shopping.yahoo.com/search;_ylt=Arcj4sDRJo.MqzKXWBasOPobFt0A?p=BMW+Owner%27s+Manual&did=	$39.95	Yahoo
11	http://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.95	Google
12	http://www.google.com/search?sourceid=navclient&aq=t&ie=UTF-8&rlz=1T4SUNA_enUS283US284&q=toyota+MR2+and+owners+and+manual	$29.95	Google
13	http://www.google.com/search?client=safari&rls=en&q=03+saab+repair+manual&ie=UTF-8&oe=UTF-8	$39.95	Google
14	http://www.google.com/search?hl=en&q=www.books4cars.com&btnG=Google+Search&aq=f&oq=	$29.95	Google
15		$39.95	#N/A
16	http://aolsearch.aol.com/aol/search?encquery=ef96b17238efbb09d10ad8858bd83efaf7c1deeb8e0fdcd7&invocationType=keyword_rollover&ie=UTF-8	$89.95	AOL
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]

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"})))