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!
LVL 1
EscanabaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Glenn RayConnect With a Mentor Excel 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
 
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
All Courses

From novice to tech pro — start learning today.