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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
521
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,089,476
Messages
5,408,473
Members
403,208
Latest member
JFoley182

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top