Return two values based on another

G

gfaulds

New Member
Joined
Jan 15, 2014
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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)),"-")
 
Upvote 0
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.
 
Upvote 0
=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))),"-")
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
help on SUM
Replies
5
Views
6K
mvptomlinson
M

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top