Find highest value out of multiple values

[Jen5309]

I have 2 columns, A (has the names of employees) and B (has the month in which the employee has a project scheduled). What I’m looking to do is find the latest month an employee has a project scheduled. Note: Employees can have a multiple number of projects so they may be listed multiple times with corresponding months.

I have each employee listed in column F and am looking to find their latest project month in column G. (I have the number that the formula should return in column H).

Thanks!

[DonkeyOte]

If you want to use formulae

G2: =MAX(IF($A$2:$A$100=$F2,$B$2:$B$100))
committed with CTRL + SHIFT + ENTER
copy down

Note: your expected result for Evan is incorrect, should be 11.

You c(sh)ould think of using a PT with Name as Row Field and Month as Data Field set to MAX.

[JBeaucaire]

This is an ARRAY formula, so enter into G2 and press CTRL-SHIFT-ENTER to confirm it. Braces { } will appear around the formula and the first answer will appear.

Then you can copy that cell down the column for the other names.

=LARGE(IF($A$1:$A$60=F2,$B$1:$B$60,""),1)

EDIT: MAX...yeah, that's even a little simpler. Good one.

[Jen5309]

The MAX formula worked great! Thanks!!!

[JazH]

Hi,

I am currently trying to extract information on a country by country basis and find out mortality rates of a country (data set has 97 diff countries and multiple records differing for each disease).

I want a formula to look at a specific country and grasp out the top 5 values so that I have the top 5 values of mortality rates for each country.

If you could help me on gathering a formula that would be great.

JH

[JBeaucaire]

Hi JazH. As per forum rules, start a new thread of your own to garner the most possible attention to it. Also, be sure to attach a workbook showing the data and the manually mocked up "results" you'd like so we can see the goal line clearly.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.