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!

DM	Employee	Score		DM	Employee	Score
Anton	5232	-		John
Kyle	1503	-		Adam
Steve	1249	-		CJ
Monica	2217	51.57%		Lisa
Morgan	8394	50.74%		Sarah
John	1777	48.96%		Amy
Leo	4281	47.01%		Kyle
Kimberly	1251	47.00%		Leo
Bill	1779	46.57%		Morgan
Gilbert	584	46.19%		Anton
John	1130	46.12%		Steve
Jane	1627	45.58%		Kimberly
Amy	1252	45.16%		Monica
Anton	1603	44.49%		Cindy
Jane	351	44.29%		Jane
Leo	2725	43.52%		Eddie
Steve	582	43.23%		Robert
Eddie	928	42.99%		Gilbert
Sarah	1739	42.93%		Matt
Steve	1749	42.62%		Bill

<colgroup><col width="64" style="width: 48pt;" span="7"> <tbody> </tbody>

 

[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>}}

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

<tbody>
</tbody>

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