ExcelBanter - Military Rate/Rank Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Military Rate/Rank Formula (https://www.excelbanter.com/excel-worksheet-functions/448715-military-rate-rank-formula.html)

Military Rate/Rank Formula

Greetings Excel Gurus,

I would first like to thank everyone who will attempt to assit me in this matter as it has been a complete pain in my ass since I first checked into my command over a month ago.

So heres what I have...

I have a Master Excel sheet that lists everyone in the command (Name, Rank, DOB, ect). However I also work with a completely seperate list that needs to be provided to another command with similary but yet different variables. As some of you may or may not know, the Navy has "Rates" that designate a persons specific type of Job. For example, I'm an IT2 (E-5) that works alongside a YN2 (also an E-5).

Heres what I want to do...

I would like to take the Master List with the "Rate" column, and create a formula to automatically convert it to the basic (E-1, E-2, E-3, ect). But since there are 64 types of rates, only the last 2 (or in E-4 to E-6 rates, 1) characters are similar. By spending alot of time doing research, I'm coming to the conclusion that I need to somehow create an "IF, RIGHT, and VLOOKUP" formula to make this work.

Anyone possibly know how to do this? I'm currently working with Excel 2007.

Quote:

Originally Posted by TechD87 (Post 1611637)

Greetings Excel Gurus,
I would first like to thank everyone who will attempt to assit me in this matter as it has been a complete pain in my ass since I first checked into my command over a month ago.
So heres what I have...
I have a Master Excel sheet that lists everyone in the command (Name, Rank, DOB, ect). However I also work with a completely seperate list that needs to be provided to another command with similary but yet different variables. As some of you may or may not know, the Navy has "Rates" that designate a persons specific type of Job. For example, I'm an IT2 (E-5) that works alongside a YN2 (also an E-5).
Heres what I want to do...
I would like to take the Master List with the "Rate" column, and create a formula to automatically convert it to the basic (E-1, E-2, E-3, ect). But since there are 64 types of rates, only the last 2 (or in E-4 to E-6 rates, 1) characters are similar. By spending alot of time doing research, I'm coming to the conclusion that I need to somehow create an "IF, RIGHT, and VLOOKUP" formula to make this work.
Anyone possibly know how to do this? I'm currently working with Excel 2007.

TechD87, Good Afternoon.

Attach a small spreadsheet with a clear example about what you desire.
Fill some data.

I believe that will be easier and quicker to give you an answer.