Solved

Excel 2007 Formula That Pulls Highest Degree From List

Posted on 2016-10-14
2
94 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

631 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