toomany if statements

Anala

New Member
Joined
Dec 1, 2003
Messages
5
Hello everybody:

I've been having a little problem with the if formula. This is my first time that i post in english so forgive me if i make to many gramatical mistakes.

well the thing is that i have data input with sales description and i want to write some especific words in one cell base on some key words in the description that is in other cell, for example

description
AF C PS 2 ITS Imp Tape VSM + 9840

and the formula is:
=IF(ISNUMBER(SEARCH("DOCUMENT",$F306)),"Mail",IF(ISNUMBER(SEARCH("MAIL",$F306)),"Mail",IF(ISNUMBER(SEARCH("VIDEO",$F306)),"VideoSur",IF(ISNUMBER(SEARCH("DCS",$F306)),"DCS",IF(ISNUMBER(FINDB("ESS",$F306)),"ESS",IF(ISNUMBER(SEARCH("ITS",$F306)),"IMP","Sales1"))))))

that will return the word IMP base on the product description above

for now i have three diferent formulas because the if statements are too many and i can nest more than 6 or 7 if statements in one formula.

So what i want is to find a way to be able to nest more than 7 if statements

hope you can help me i already check previous posts and i couldn't fine anything... :(

thanks..

Anala
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Something like...
Book1
ABCDEF
1MailMail
2AFCPS2ITSImpTapeVSM+9840IMPVideoVideoSur
3dfVideo6NoTape+8786VideoSurDCSDCS
4AFFPS2VideoImp+1000VideoSurESSESS
5AFESSIMPnotapeESSITSIMP
6
Sheet1


The formula in B2 is:

=INDEX($F$1:$F$5,MATCH(TRUE,ISNUMBER(SEARCH($E$1:$E$5,A2)),0))

which must be confirmed with control+shift+enter instead of just with enter.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
About time Aladin showed up with one of his magic array formulas. I was bustin' my head tryin' to figure out what it should look like. (y)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,448
Members
425,546
Latest member
DisMissive

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