Help with IF Function !!

[gprogers]

Trying to setup following function:

=IF(C4="AC","1",IF(C4="LAC","2",IF(C4="CPL","3",IF(C4="SGT","4",IF(C4="FSGT","5",IF(C4="WOFF","6",IF(C4="FLGOFF","7",IF(C4="FLTLT","8",IF(C4="SQNLDR","9",IF(C4="WGCDR","10",IF(C4="ACW","1",IF(C4="LACW","2","")))

Basically want values in cell C4 to trigger a number to be allocated in C2 depending on the rank.

Coming up with error....need help.

 

[sheetspread]

Is a separate index table out of the question? Overstuffed formulas are a staple among excel masochists, your only other options seem to be a reverse choose function or select case macro.

 

[gprogers]

Just to add to this...it seems that the problem comes about when the formula bar moves down a line after the 8th entry...not sure

 

[gprogers]

Thanks mate, I figured the extra long formula was posing an issue. Not too sure on index tables or reverse choose functions though, bit of an amateur.

Appreciate the assistance.

 

[Michael M]

Hi gprogers and Welcome to the Board
Depending on the version you are using you may have exceeded the IF limit ( it's 7 IF's in 2003 or older)
I agree with sheetspread...however, if you want a formula, try

=LOOKUP(C4,{"AC","LAC","CPL","SGT","FSGT","WOFF","FLGOFF","FLTLT","SQNLDR","WGCDR","ACW","LACW",""},{1,2,3,4,5,6,7,8,9,10,1,2,""})

Ooops, got a bit carried away with my inserting.......the formula will fail if the cell is blank !!
this will be better

=IF(C4="","",LOOKUP(C4,{"AC","LAC","CPL","SGT","FSGT","WOFF","FLGOFF","FLTLT","SQNLDR","WGCDR","ACW","LACW"},{1,2,3,4,5,6,7,8,9,10,1,2}))

 

[gprogers]

Michael,

thanks for the response. that formula works wonderfully - i really appreciate it. thanks too 'sheetspread' for your help.

I'm a new member to this forum and I am very impressed with the help people provide in such little time.

nice work guys.

 

[gprogers]

Michael,

Just noticed that with that formula, a few number are being thrown out. for example, there are a few SGT's that now have number 8 next to them instead of 4. any thoughts?

 

[Michael M]

Glad it worked for you....hope you picked up on the error in my first "attempt" and used the 2nd version.

 

[gprogers]

lol disregard.....just saw your repost.

thanks again mate. champion.

 

[gprogers]

okay sorry - i may be doing something really dumb here but if i click and drag that formula down the entire column - it seems the numbers are being assigned randomly....aargghh - i bet its something simple too.