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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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.
 
Upvote 0

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)
 
Upvote 0

Forum statistics

Threads
1,187,187
Messages
5,962,101
Members
438,583
Latest member
DJTCharts

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