Too many IF's Help!!

M

MIexcel

New Member
Joined
Feb 11, 2010
Messages
2
Hello I have a formula that finds the brands in the title of a list of products. I was wondering if a Function can be created or theres another way around this.

My formula is
=IF(Inventory[[#This Row],[QB Title]]="","",
IF(ISNUMBER(SEARCH("kensington",Inventory[[#This Row],[QB Title]])),"Kensington",
IF(ISNUMBER(SEARCH("Vipre",Inventory[[#This Row],[QB Title]])),"Vipre",
IF(ISNUMBER(SEARCH("norton",Inventory[[#This Row],[QB Title]])),"Symantec",
IF(ISNUMBER(SEARCH("cyberpatrol",Inventory[[#This Row],[QB Title]])),"CyberPatrol",
IF(ISNUMBER(SEARCH("pc tools",Inventory[[#This Row],[QB Title]])),"PC Tools",
IF(ISNUMBER(SEARCH("Acronis",Inventory[[#This Row],[QB Title]])),"Acronis",
IF(ISNUMBER(SEARCH("nero",Inventory[[#This Row],[QB Title]])),"Ahead",
IF(ISNUMBER(SEARCH("Activision",Inventory[[#This Row],[QB Title]])),"Activision",
IF(ISNUMBER(SEARCH("Adobe",Inventory[[#This Row],[QB Title]])),"Adobe",
IF(ISNUMBER(SEARCH("Ahead",Inventory[[#This Row],[QB Title]])),"Ahead",
IF(ISNUMBER(SEARCH("Apple",Inventory[[#This Row],[QB Title]])),"Apple",
IF(ISNUMBER(SEARCH("Autodesk",Inventory[[#This Row],[QB Title]])),"Autodesk",
IF(ISNUMBER(SEARCH("AVG",Inventory[[#This Row],[QB Title]])),"AVG",
IF(ISNUMBER(SEARCH("Bitdefender",Inventory[[#This Row],[QB Title]])),"Bitdefender",
IF(ISNUMBER(SEARCH("Broderbund",Inventory[[#This Row],[QB Title]])),"Broderbund",
IF(ISNUMBER(SEARCH("Corel",Inventory[[#This Row],[QB Title]])),"Corel",
IF(ISNUMBER(SEARCH("CyberLink",Inventory[[#This Row],[QB Title]])),"CyberLink",
IF(ISNUMBER(SEARCH("Diskeeper",Inventory[[#This Row],[QB Title]])),"Diskeeper",
IF(ISNUMBER(SEARCH("Efrontier",Inventory[[#This Row],[QB Title]])),"Efrontier",
IF(ISNUMBER(SEARCH("ESET",Inventory[[#This Row],[QB Title]])),"ESET",
IF(ISNUMBER(SEARCH("GTI",Inventory[[#This Row],[QB Title]])),"GTI",
IF(ISNUMBER(SEARCH("H&R Block",Inventory[[#This Row],[QB Title]])),"H&R Block",
IF(ISNUMBER(SEARCH("IBM",Inventory[[#This Row],[QB Title]])),"IBM",
IF(ISNUMBER(SEARCH("Individual Software",Inventory[[#This Row],[QB Title]])),"Individual Software",
IF(ISNUMBER(SEARCH("Intuit",Inventory[[#This Row],[QB Title]])),"Intuit",
IF(ISNUMBER(SEARCH("Nolo ",Inventory[[#This Row],[QB Title]])),"Intuit",
IF(ISNUMBER(SEARCH("Iolo",Inventory[[#This Row],[QB Title]])),"Iolo",
IF(ISNUMBER(SEARCH("Kaspersky",Inventory[[#This Row],[QB Title]])),"Kaspersky",
IF(ISNUMBER(SEARCH("McAfee",Inventory[[#This Row],[QB Title]])),"McAfee",
IF(ISNUMBER(SEARCH("Microsoft",Inventory[[#This Row],[QB Title]])),"Microsoft",
IF(ISNUMBER(SEARCH("Nintendo Wii",Inventory[[#This Row],[QB Title]])),"Nintendo Wii",
IF(ISNUMBER(SEARCH("Nova Development",Inventory[[#This Row],[QB Title]])),"Nova Development",
IF(ISNUMBER(SEARCH("Nuance",Inventory[[#This Row],[QB Title]])),"Nuance",
IF(ISNUMBER(SEARCH("Palo Alto",Inventory[[#This Row],[QB Title]])),"Palo Alto",
IF(ISNUMBER(SEARCH("Panda",Inventory[[#This Row],[QB Title]])),"Panda",
IF(ISNUMBER(SEARCH("Parallels",Inventory[[#This Row],[QB Title]])),"Parallels",
IF(ISNUMBER(SEARCH("PC Treasures",Inventory[[#This Row],[QB Title]])),"PC Treasures",
IF(ISNUMBER(SEARCH("QuarkXpress",Inventory[[#This Row],[QB Title]])),"QuarkXpress",
IF(ISNUMBER(SEARCH("Roxio",Inventory[[#This Row],[QB Title]])),"Roxio",
IF(ISNUMBER(SEARCH("Sage",Inventory[[#This Row],[QB Title]])),"Sage",
IF(ISNUMBER(SEARCH("ScanSoft",Inventory[[#This Row],[QB Title]])),"ScanSoft",
IF(ISNUMBER(SEARCH("Serif",Inventory[[#This Row],[QB Title]])),"Serif",
IF(ISNUMBER(SEARCH("SmithMicro",Inventory[[#This Row],[QB Title]])),"SmithMicro",
IF(ISNUMBER(SEARCH("Smith Micro",Inventory[[#This Row],[QB Title]])),"SmithMicro",
IF(ISNUMBER(SEARCH("Sony",Inventory[[#This Row],[QB Title]])),"Sony",
IF(ISNUMBER(SEARCH("Sunbelt Software",Inventory[[#This Row],[QB Title]])),"Sunbelt Software",
IF(ISNUMBER(SEARCH("Symantec",Inventory[[#This Row],[QB Title]])),"Symantec",
IF(ISNUMBER(SEARCH("Trend Micro",Inventory[[#This Row],[QB Title]])),"Trend Micro",
IF(ISNUMBER(SEARCH("TuneUp",Inventory[[#This Row],[QB Title]])),"TuneUp",
IF(ISNUMBER(SEARCH("Viva Media",Inventory[[#This Row],[QB Title]])),"Viva Media",
IF(ISNUMBER(SEARCH("VMWare",Inventory[[#This Row],[QB Title]])),"VMWare",
IF(ISNUMBER(SEARCH("Webroot",Inventory[[#This Row],[QB Title]])),"Webroot",
IF(ISNUMBER(SEARCH("Individual",Inventory[[#This Row],[QB Title]])),"Individual Software",
IF(ISNUMBER(SEARCH("CA ",Inventory[[#This Row],[QB Title]])),"CA",
IF(ISNUMBER(SEARCH("Nitro",Inventory[[#This Row],[QB Title]])),"Nitro",
IF(ISNUMBER(SEARCH("ZoneAlarm",Inventory[[#This Row],[QB Title]])),"ZoneAlarm",
IF(ISNUMBER(SEARCH("Learning Company",Inventory[[#This Row],[QB Title]])),"The Learning Company",
IF(ISNUMBER(SEARCH("Knowledge Adventure",Inventory[[#This Row],[QB Title]])),"Knowledge Adventure",
IF(ISNUMBER(SEARCH("Brighter Minds",Inventory[[#This Row],[QB Title]])),"Brighter Minds",
IF(ISNUMBER(SEARCH("Disney",Inventory[[#This Row],[QB Title]])),"Disney",
IF(ISNUMBER(SEARCH("Scholastic",Inventory[[#This Row],[QB Title]])),"Scholastic",
IF(ISNUMBER(SEARCH("TRENDnet",Inventory[[#This Row],[QB Title]])),"TRENDnet",
"NO DATA!")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

It would be great if a function can replace this.

Thanks for your help
Tony
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Create a range with the search strings like kensington, wipre, etc., name that range KeyWords, and invoke:
Code: 
=LOOKUP(9.99999999999999E+307,
    SEARCH(KeyWords,Inventory[[#This Row],[QB Title]]),KeyWords)
 
Upvote 0
Hey Aladin,
Thanks for your help, it works great but just one question, what does that number 9.99999999999999E+307 suppose to mean? I replace that with a 1 and the formula still works, is that number necessary?

Thanks for your help
 
Last edited:
Upvote 0
MIexcel said:
Hey Aladin,
Thanks for your help, it works great but just one question, what does that number 9.99999999999999E+307 suppose to mean? I replace that with a 1 and the formula still works, is that number necessary?

Thanks for your help
Click to expand...

SEARCH yields an array of integers (positions) and #VALUE! errors.

LOOKUP with that big number as the lookup value determines the last integer from the array SEARCH yields and returns the corresponding value from KeyWords. Example:

LOOKUP(BigNum,{#VALUE!,12,#VALUE!},{"A","F","Z"})

The last numeric value in {#VALUE!,12,#VALUE!} is 12. "F" is the symbol that corresponds to 12. So the result will be: F.

See also:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/forum/showthread.php?t=310278
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

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
Back
Top