Hiya, I'm a newby to the group and I've got a question.

  • Thread starter Thread starter Chris N
  • Start date Start date
C

Chris N

I'm trying to get the "IF" function to abbreviate a list of ranks for
multiple individuals. The problem is I have 13 ranks and the "IF" is limited
to 7. I split the functions into two Named Formulas. I then use
=(NamedF1,NamedF2) to get the correct rank. One cell will give me a valid
rank, but the other one gives me FALSE. How can I ask Excel to only give the
correct value and ignore the FALSE?

This is what I have:

=IF(Test_Sheet!E11="Cadet","Cdt",IF(Test_Sheet!E11="Sergeant","Sgt",IF(Test_
Sheet!E11="2nd Lieutenant","2Lt",IF(Test_Sheet!E11="1st
Lieutenant","1Lt",IF(Test_Sheet!E11="Captain","Cpt",IF(Test_Sheet!E11="Major
","Maj"))))))

And

=IF(Test_Sheet!E11="Lieutenant
Colonel","LtC",IF(Test_Sheet!E11="Colonel","Col",IF(Test_Sheet!E11="Brigadie
r General","B Gen",IF(Test_Sheet!E11="Major General","M
Gen",IF(Test_Sheet!E11="Lieutenant General","Lt
Gen",IF(Test_Sheet!E11="General","Gen",IF(Test_Sheet!E11="Field
Marshall","FM")))))))


Thank you for your time : )

Chris
 
Chris,

Use VLOOKUP

Place the full names in one column and the abbreviations in the next column
on the right.

VLOOKUP("lookup value","table range",2,False)

HTH

PC
 
Hi Don,

Thanks for the reply. No I'm not military, I'm trying to compile scores. I
gather all our player scores from a url's, using "GET DATA" then compile
them to one sheet which I post to the internet. Everything works fine, but
my "Rank" cell is too large. I then thought about abbreviating the ranks.
Thus my original post.

I'll have a look at VLOOUP.

Thanks!

Chris
 
Back
Top