With VBA custom function. I made the data into a table, you can replace 'Table7' with A2:D23 in this example.
Book1 |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
1 | Pay Grade | Rank | Abbreviation | Classification | | Name | Grade | Abbr |
---|
2 | E-1 | Airman Basic | AB | Enlisted Airman | | Bill | O-8 | Maj G |
---|
3 | E-2 | Airman | Amn | Enlisted Airman | | Steve | O-7 | Brig |
---|
4 | E-3 | Airman First Class | A1C | Enlisted Airman | | Gary | O-10 | Gen, GAF |
---|
5 | E-4 | Senior Airman | SrA | Enlisted Airman | | Craig | E-7 | MSgt |
---|
6 | E-5 | Staff Sergeant | SSgt | Noncommissioned Officer | | Adam | O-8 | Maj G |
---|
7 | E-6 | Technical Sergeant | TSgt | Noncommissioned Officer | | Gio | E-9 | CMSgt, CCM, CMSAF |
---|
8 | E-7 | Master Sergeant | MSgt | Noncommissioned Officer | | | | |
---|
9 | E-8 | Senior Master Sergeant | SMSgt | Noncommissioned Officer | | | | |
---|
10 | E-9 | Chief Master Sergeant | CMSgt | Noncommissioned Officer | | | | |
---|
11 | E-9 | Command Chief Master Sergeant | CCM | Noncommissioned Officer | | | | |
---|
12 | E-9 | Chief Master Sergeant Of The Air Force | CMSAF | Noncommissioned Officer (Special) | | | | |
---|
13 | O-1 | Second Lieutenant | 2d Lt | Commissioned Officer | | | | |
---|
14 | O-2 | First Lieutenant | 1st L | Commissioned Officer | | | | |
---|
15 | O-3 | Captain | Capt | Commissioned Officer | | | | |
---|
16 | O-4 | Major | Maj | Field Officer | | | | |
---|
17 | O-5 | Lieutenant Colonel | Lt Co | Field Officer | | | | |
---|
18 | O-6 | Colonel | Col | Field Officer | | | | |
---|
19 | O-7 | Brigadier General | Brig | General Officer | | | | |
---|
20 | O-8 | Major General | Maj G | General Officer | | | | |
---|
21 | O-9 | Lieutenant General | Lt Ge | General Officer | | | | |
---|
22 | O-10 | General | Gen | General Officer | | | | |
---|
23 | O-10 | General of the Air Force | GAF | General Officer | | | | |
---|
|
---|
VBA Code:
Function GRADES(iLookup As String, Tbl As Range)
Dim AR() As Variant: AR = Tbl.Value
With CreateObject("System.Collections.ArrayList")
For i = LBound(AR) To UBound(AR)
If AR(i, 1) = iLookup Then .Add AR(i, 3)
Next i
GRADES = Join(.ToArray, ", ")
End With
End Function
Here is what I have...
=IF(C2=1,"2 LT",IF(C2=2,"1 LT",IF(C2=3,"Capt",IF(C2=4,"Maj",IF(C2=5,"Lt Col",IF(C2=6,"Col",IF(C2=7,"Brig Gen",IF(C2=8,"Maj Gen",IF(C2=9,"Lt Gen",IF(C2=10,"Gen",IF(C2=31,"E1",IF(C2=32,"E2",IF(C2=33,"E3",IF(C2=34,"E4",IF(C2=35,"E5",IF(C2=36,"E6",IF(C2=37,"E7",IF(C2=38,"E8",IF(C2=39,"E9",IF(C2="","CIV"))))))))))))))))))))
And it's working now, not sure why it didn't work on my original worksheet