Return two values based on another

[gfaulds]

Hello,

I'm trying to be able to return data from two columns based on a value on the third. The list below shows a DM name, the employee ID number and their score. What I want to produce is a list where for each DM, I return the employee name and score based on the highest score. So in the data below for John, it should return employee 1777 and score 48.96%.

Thank you for any help!

[TABLE="width: 336"]
<colgroup><col width="64" style="width: 48pt;" span="7"> <tbody>[TR]
[TD="class: xl68, width: 64, bgcolor: #DDEBF7"]DM[/TD]
[TD="class: xl68, width: 64, bgcolor: #DDEBF7"]Employee[/TD]
[TD="class: xl68, width: 64, bgcolor: #DDEBF7"]Score[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 64, bgcolor: yellow"]DM[/TD]
[TD="class: xl67, width: 64, bgcolor: yellow"]Employee[/TD]
[TD="class: xl67, width: 64, bgcolor: yellow"]Score[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Anton[/TD]
[TD="class: xl66, bgcolor: transparent"]5232[/TD]
[TD="class: xl66, bgcolor: transparent"]-[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]John[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Kyle[/TD]
[TD="class: xl66, bgcolor: transparent"]1503[/TD]
[TD="class: xl66, bgcolor: transparent"]-[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Adam[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Steve[/TD]
[TD="class: xl66, bgcolor: transparent"]1249[/TD]
[TD="class: xl66, bgcolor: transparent"]-[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]CJ[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Monica[/TD]
[TD="class: xl66, bgcolor: transparent"]2217[/TD]
[TD="class: xl69, bgcolor: transparent"]51.57%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Lisa[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Morgan[/TD]
[TD="class: xl66, bgcolor: transparent"]8394[/TD]
[TD="class: xl69, bgcolor: transparent"]50.74%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]John[/TD]
[TD="class: xl66, bgcolor: transparent"]1777[/TD]
[TD="class: xl69, bgcolor: transparent"]48.96%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Amy[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Leo[/TD]
[TD="class: xl66, bgcolor: transparent"]4281[/TD]
[TD="class: xl69, bgcolor: transparent"]47.01%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Kyle[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Kimberly[/TD]
[TD="class: xl66, bgcolor: transparent"]1251[/TD]
[TD="class: xl69, bgcolor: transparent"]47.00%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Leo[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Bill[/TD]
[TD="class: xl66, bgcolor: transparent"]1779[/TD]
[TD="class: xl69, bgcolor: transparent"]46.57%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Morgan[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Gilbert[/TD]
[TD="class: xl66, bgcolor: transparent"]584[/TD]
[TD="class: xl69, bgcolor: transparent"]46.19%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Anton[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]John[/TD]
[TD="class: xl66, bgcolor: transparent"]1130[/TD]
[TD="class: xl69, bgcolor: transparent"]46.12%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Steve[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Jane[/TD]
[TD="class: xl66, bgcolor: transparent"]1627[/TD]
[TD="class: xl69, bgcolor: transparent"]45.58%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Kimberly[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Amy[/TD]
[TD="class: xl66, bgcolor: transparent"]1252[/TD]
[TD="class: xl69, bgcolor: transparent"]45.16%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Monica[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Anton[/TD]
[TD="class: xl66, bgcolor: transparent"]1603[/TD]
[TD="class: xl69, bgcolor: transparent"]44.49%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Cindy[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Jane[/TD]
[TD="class: xl66, bgcolor: transparent"]351[/TD]
[TD="class: xl69, bgcolor: transparent"]44.29%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Jane[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Leo[/TD]
[TD="class: xl66, bgcolor: transparent"]2725[/TD]
[TD="class: xl69, bgcolor: transparent"]43.52%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Eddie[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Steve[/TD]
[TD="class: xl66, bgcolor: transparent"]582[/TD]
[TD="class: xl69, bgcolor: transparent"]43.23%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Robert[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Eddie[/TD]
[TD="class: xl66, bgcolor: transparent"]928[/TD]
[TD="class: xl69, bgcolor: transparent"]42.99%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Gilbert[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl66, bgcolor: transparent"]1739[/TD]
[TD="class: xl69, bgcolor: transparent"]42.93%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Matt[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Steve[/TD]
[TD="class: xl66, bgcolor: transparent"]1749[/TD]
[TD="class: xl69, bgcolor: transparent"]42.62%[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Bill[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Heres a way with those tables copied and pasted into A1:

in F2: =IF(COUNTIFS($A$2:$A$21,E2),MAX(INDEX(($A$2:$A$21=E2)*$B$2:$B$21,0)),"-")
in G2: =IF(ISNUMBER(F2),INDEX($C$2:$C$21,MATCH(1,INDEX(($A$2:$A$21=E2)*($B$2:$B$21=F2),0),0)),"-")

 

[gfaulds]

Thanks for the help. It looks like its returning the highest value in B (employee ID) instead of C (score). The goal is tor return the highest score and corresponding Employee ID for each DM. I think we are close.

really appreciate it so far.

 

[steve the fish]

Will it always be a sorted list of scores?

 

[steve the fish]

=IF(ISNUMBER(G2),INDEX($B$2:$B$21,MATCH(1,INDEX(($A$2:$A$21=E2)*($C$2:$C$21=G2),0),0)),"-")

Then this which requires CTRL-SHIFT-ENTER:

=IF(COUNTIFS($A$2:$A$21,E2,$C$2:$C$21,"<>-"),MAX(IF($A$2:$A$21=E2,IF(ISNUMBER($C$2:$C$21),$C$2:$C$21))),"-")

 

[gfaulds]

Not all the time. I'm just trying to automate having to look them all up myself. The table is actually much larger, I just reduced it down as an example.

Thanks for the follow-up.

 

[markmzz]

Hi!

Try the Array Formulas below too.

In G2 and copy down - use Ctrl+Shift+Enter to enter the formula

=IFERROR(LARGE(IF(E2=A$2:A$21,C$2:C$21),1),"")

In F2 and copy down - use Ctrl+Shift+Enter to enter the formula

=IF(G2="","",INDEX(B$2:B$21,MATCH(E2&G2,A$2:A$21&C$2:C$21,0)))_{^{<strike>
</strike>}}

[TABLE="class: grid, width: 470"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]DM[/TD]
[TD="align: center"]Employee[/TD]
[TD="align: center"]Score[/TD]
[TD="align: center"][/TD]
[TD="align: center"]DM[/TD]
[TD="align: center"]Employee[/TD]
[TD="align: center"]Score[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Anton[/TD]
[TD="align: center"]5232[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"][/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]1777[/TD]
[TD="align: center"]48,96%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Kyle[/TD]
[TD="align: center"]1503[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Adam[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Steve[/TD]
[TD="align: center"]1249[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"][/TD]
[TD="align: center"]CJ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Monica[/TD]
[TD="align: center"]2217[/TD]
[TD="align: center"]51,57%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Lisa[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Morgan[/TD]
[TD="align: center"]8394[/TD]
[TD="align: center"]50,74%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sarah[/TD]
[TD="align: center"]1739[/TD]
[TD="align: center"]42,93%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]1777[/TD]
[TD="align: center"]48,96%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Amy[/TD]
[TD="align: center"]1252[/TD]
[TD="align: center"]45,16%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Leo[/TD]
[TD="align: center"]4281[/TD]
[TD="align: center"]47,01%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Kyle[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Kimberly[/TD]
[TD="align: center"]1251[/TD]
[TD="align: center"]47,00%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Leo[/TD]
[TD="align: center"]4281[/TD]
[TD="align: center"]47,01%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Bill[/TD]
[TD="align: center"]1779[/TD]
[TD="align: center"]46,57%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Morgan[/TD]
[TD="align: center"]8394[/TD]
[TD="align: center"]50,74%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]Gilbert[/TD]
[TD="align: center"]584[/TD]
[TD="align: center"]46,19%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Anton[/TD]
[TD="align: center"]1603[/TD]
[TD="align: center"]44,49%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]1130[/TD]
[TD="align: center"]46,12%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Steve[/TD]
[TD="align: center"]582[/TD]
[TD="align: center"]43,23%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]1627[/TD]
[TD="align: center"]45,58%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Kimberly[/TD]
[TD="align: center"]1251[/TD]
[TD="align: center"]47,00%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]Amy[/TD]
[TD="align: center"]1252[/TD]
[TD="align: center"]45,16%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Monica[/TD]
[TD="align: center"]2217[/TD]
[TD="align: center"]51,57%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]Anton[/TD]
[TD="align: center"]1603[/TD]
[TD="align: center"]44,49%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Cindy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]351[/TD]
[TD="align: center"]44,29%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]1627[/TD]
[TD="align: center"]45,58%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]Leo[/TD]
[TD="align: center"]2725[/TD]
[TD="align: center"]43,52%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Eddie[/TD]
[TD="align: center"]928[/TD]
[TD="align: center"]42,99%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]Steve[/TD]
[TD="align: center"]582[/TD]
[TD="align: center"]43,23%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Robert[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]Eddie[/TD]
[TD="align: center"]928[/TD]
[TD="align: center"]42,99%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Gilbert[/TD]
[TD="align: center"]584[/TD]
[TD="align: center"]46,19%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]Sarah[/TD]
[TD="align: center"]1739[/TD]
[TD="align: center"]42,93%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Matt[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]Steve[/TD]
[TD="align: center"]1749[/TD]
[TD="align: center"]42,62%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Bill[/TD]
[TD="align: center"]1779[/TD]
[TD="align: center"]46,57%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]******[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[gfaulds]

Works like a treat! Thanks for all the help!

 

[markmzz]

Works like a treat! Thanks for all the help!

You are welcome and thanks for the return.

Markmzz