Solved

Excel 2007 Formula That Pulls Highest Degree From List

Posted on 2016-10-14
2
60 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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