Solved

Excel 2007 Formula That Pulls Highest Degree From List

Posted on 2016-10-14
2
39 Views
Last Modified: 2016-11-02
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
Comment
Question by:Escanaba
2 Comments
 
LVL 8

Expert Comment

by:Koen
ID: 41843782
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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 41847380
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Powershell merging excel spreadsheets into one 2 21
Importing csv to excel - text formating 14 23
Excel 6 19
Add macros on Open 9 33
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now