Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 102
  • Last Modified:

Excel 2007 Formula That Pulls Highest Degree From List

Hello,

Let's say column A I have a list of employee names and in column B I have employee education degrees.  Since some employees have multiple degrees (Associates, Bachelors, Masters, etc.) there are multiple rows per employee.

What formula do you recommend that would return the highest degree an employee has earned?  For example:  A1:A3 read John Doe and B1:B3 reads High School, Associate, Bachelors.  The end result I want to see is just the employee name and Bachelors.

Thanks!
0
Escanaba
Asked:
Escanaba
1 Solution
 
KoenCommented:
finding the highest degree is simpel, once you define the order...Excel can only compare when a ranking has been established.
You will probably need a reference table in which you assign a numeric value to sort the degrees.

Once that is done, a lookup can assign the value to the degree, and a maxif formula can find you the highest degree per person.
1
 
Glenn RayExcel VBA DeveloperCommented:
Unfortunately, there is no  "MAXIF" formula.  You can, however create an array function that replicates that logic.

However, Koen is correct in that you'll want to set up a lookup table that has ALL the possible degree names listed in order of hierarchy.  Your data set, as you described, would have employee names in one column and all applicable degrees listed in the adjacent column.  And, as you indicated, there may be multiple listings for an employee if they have more than one degree.

You then would have an input cell where you'd populate an employee's name.  If that person were on the list, there would be a lookup function that would return the highest degree, based on your lookup table.

It will help to have a "Rank" column adjacent to your employee data that shows the numerical value associated with each degree (say, 0=no high school, 1=GED, 2=High School, 3=Associates, and so on).

Finally, you'll set up an array formula using INDEX, MAX, IF to return the value.

I've created an example file (attached).  The final formula, referencing a name entered in cell B2, looking up employees on a sheet titled [Data] and having an Excel Table called "tblDegrees" looks like this:
{=INDEX(tblDegrees,MAX(IF(Data!$A$2:$A$22=B2,Data!$C$2:$C$22,0))+1)}
This is an array formula and must be entered using [Shift]+[Ctrl]+[Enter] to work.  The curly brackets {} will appear around it.
EE-Q_28976451.xlsx
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now