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?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
532
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

ADVERTISEMENT

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

ADVERTISEMENT

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,136,336
Messages
5,675,184
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top