Get name of person with highest salary?

[Rudi]

Hi,

I have a list that contains typical employee details... Name, Hire Date, Job Description, Salary.

How can I find the name of the person with the highest or second highest salary. I tried using =OFFSET(LARGE(...)) , but OFFSET is not an array function.

Assume the list as with the labels above starting with the labels in A1.


I was trying in cell E2:
=OFFSET(LARGE(D2,1),0,-4)

this would have been great if it collected the name in the A column for that row. It seems to be a complex detail if I had to use VLOOKUP, but there are duplicate salaries so this will not work.

Any bright ideas??

TX

[mbarron]

Something like this perhaps:
=INDEX(A2:A100,MATCH(LARGE(D2:D100,1),D2:D100,0))

[HansV]

The following is a standard formula, not an array formula.
Assumptions:
- Names in A2:A30.
- Salaries in D2:D30
- n = 1 for the highest salary, n = 2 for the second highest etc.

=INDEX($A$2:$A$30,MATCH(LARGE($D$2:$D$30,n),$D$2:$D$30,0))

If there are ties, the formula will return the first name it finds.

[Rudi]

TX a stack Mbarron and Hans.

The only concern now is if there are duplicate salaries! This could return the wrong name?

It will be great if there is a workaround to this?? For now...I am grateful for the recommended formula. Cheers

[HansV]

You could use the following array formula (confirm with Ctrl+Shift+Enter):

=INDEX($A$2:$A$30,MATCH(LARGE($E$2:$E$30+ROW($E$2:$E$30)/10000000000,I8),$E$2:$E$30+ROW($E$2:$E$30)/10000000000,0))

The 10000000000 should be sufficiently large compared to the salaries.

[mbarron]

The following assume the Large value is in G1 and the list of employees who's salaries a at that rank starts in G2
For the first person on the list (G2):

Code: Select all

=INDEX($A$2:$A$27,MATCH(LARGE($D$2:$D$27,$G$1),$D$2:$D$27,0))

For the rest (starting in G3 and copied down
(xl07)

Code: Select all

=IFERROR(INDEX(OFFSET($A$2,MATCH(G2,$A$2:$A$27,0),0,ROWS(A2:A27)-MATCH(G2,$A$2:$A$27,0),1),MATCH(LARGE($D$2:$D$27,$G$1),OFFSET($A$2,MATCH(G2,$A$2:$A$27,0),3,ROWS(A2:A27)-MATCH(G2,$A$2:$A$27,0),1),0)),"")

(xl03 and prior)

Code: Select all

=IF(ISERROR(INDEX(OFFSET($A$2,MATCH(H2,$A$2:$A$27,0),0,ROWS(B2:B27)-MATCH(H2,$A$2:$A$27,0),1),MATCH(LARGE($D$2:$D$27,$G$1),OFFSET($A$2,MATCH(H2,$A$2:$A$27,0),3,ROWS(B2:B27)-MATCH(H2,$A$2:$A$27,0),1),0))),"",INDEX(OFFSET($A$2,MATCH(H2,$A$2:$A$27,0),0,ROWS(B2:B27)-MATCH(H2,$A$2:$A$27,0),1),MATCH(LARGE($D$2:$D$27,$G$1),OFFSET($A$2,MATCH(H2,$A$2:$A$27,0),3,ROWS(B2:B27)-MATCH(H2,$A$2:$A$27,0),1),0)))

[Rudi]

Wow...what a formula(s)?!

I will have to try this on the genuine data. For now...many thanks for all the effort to work out that details.

Cheers Hans and MBarron! I appreciate the assistance!