output value of

[DDT123]

Greetings - I have 2 worksheets... the source sheet which includes raw data and the report sheet which is presented to leadership.
The source sheet has agent names in column A, project duration in column D, and project description in column E.
The report sheet has an agent's name listed in column A, and the sum of all project durations in column B.

I'm looking for the formula which would lookup or match the agent name listed in column A of the report sheet, find the project which had the longest duration (column D of the source sheet), then output the project description in column C of the report sheet from column E of the source sheet.

Any help with this formula would be greatly appreciated!

 

[DDT123]

Sorry guys, I posted this without finishing the thread title and i don't see how i can edit it or delete it

 

[Asbestos_Jen]

Try this out:

Source data:

Book1
	A	B	C	D	E
1	Agent name			Proj duration	Proj description
2	Jen			21	qwer
3	John			14	asdf
4	Barb			7	zxcv
5	Billy			17	uiop
6	Jen			3	hjkl
7	John			10	dsgf
8	Barb			19	sdfu
9	Jen			21	tuij
10	John			3	poia
11	Barb			1	wery
12	Billy			9	rtyu
Source

Report:

=MAXIFS(Source!$D$2:$D$12,Source!$A$2:$A$12,A2)

=XLOOKUP(A2&C2,Source!$A$2:$A$12&Source!$D$2:$D$12,Source!$E$2:$E$12,,)

 

[hagia_sofia]

Another option:

=LET(
a,FILTER(Source!D2:E100,Source!A2:A100=Report!A2),
b,CHOOSECOLS(a,1),
FILTER(CHOOSECOLS(a,2),b=MAX(b)))

 

[DDT123]

Try this out:

Source data:

Book1
	A	B	C	D	E
1	Agent name			Proj duration	Proj description
2	Jen			21	qwer
3	John			14	asdf
4	Barb			7	zxcv
5	Billy			17	uiop
6	Jen			3	hjkl
7	John			10	dsgf
8	Barb			19	sdfu
9	Jen			21	tuij
10	John			3	poia
11	Barb			1	wery
12	Billy			9	rtyu
Source

Report:

=MAXIFS(Source!$D$2:$D$12,Source!$A$2:$A$12,A2)

=XLOOKUP(A2&C2,Source!$A$2:$A$12&Source!$D$2:$D$12,Source!$E$2:$E$12,,)

View attachment 113981

Thank you! This worked great!!

 

[Asbestos_Jen]

Glad to have helped; thanks for the feedback. Just a note: if there are two projects with the same duration, it will return the description of the first one in the list. For example, in the source data I used, there are two projects of duration 21 for Jen, and it returned only the first one.

 

[Asbestos_Jen]

Another option:

=LET(
a,FILTER(Source!D2:E100,Source!A2:A100=Report!A2),
b,CHOOSECOLS(a,1),
FILTER(CHOOSECOLS(a,2),b=MAX(b)))

I'm learning the new functions using the Excel 365 web app. I expanded my data set to row 100 and compared my formulas to yours. I only get 3 agent names, and have different results for the 2nd and 3rd when it spills. Can you explain what's happening?

Book1
	A			D	E
1	Agent name			Proj duration	Proj description
2	Jen			21	a
3	John			14	b
4	Barb			7	c
5	Billy			17	d
6	Jen			3	e
7	John			10	f
8	Barb			19	g
9	Jen			21	h
10	John			3	i
11	Barb			1	j
12	Billy			9	k
13	Jen			28	l
14	John			2	m
15	Barb			17	n
16	Billy			7	o
17	Jen			14	p
18	John			25	q
19	Barb			3	r
20	Jen			8	s
21	John			3	t
22	Barb			3	u
23	Billy			6	v
24	Jen			25	w
25	John			25	x
26	Barb			10	y
27	Billy			9	z
28	Jen			17	aa
29	John			8	ab
30	Barb			23	ac
31	Jen			22	ad
32	John			9	ae
33	Barb			19	af
34	Billy			16	ag
35	Jen			16	ah
36	John			28	ai
37	Barb			6	aj
38	Billy			13	ak
39	Jen			5	al
40	John			3	am
41	Barb			2	an
42	Jen			27	ao
43	John			14	ap
44	Barb			26	aq
45	Billy			5	ar
46	Jen			28	as
47	John			12	at
48	Barb			2	au
49	Billy			17	av
50	Jen			1	aw
51	John			5	ax
52	Barb			4	ay
53	Jen			28	az
54	John			28	ba
55	Barb			10	bb
56	Billy			19	bc
57	Jen			18	bd
58	John			22	be
59	Barb			4	bf
60	Billy			9	bg
61	Jen			27	bh
62	John			8	bi
63	Barb			28	bj
64	Jen			2	bk
65	John			22	bl
66	Barb			3	bm
67	Billy			6	bn
68	Jen			23	bo
69	John			22	bp
70	Barb			14	bq
71	Billy			14	br
72	Jen			5	bs
73	John			25	bt
74	Barb			23	bu
75	Jen			18	bv
76	John			12	bw
77	Barb			1	bx
78	Billy			19	by
79	Jen			18	bz
80	John			7	ca
81	Barb			27	cb
82	Billy			23	cc
83	Jen			14	cd
84	John			7	ce
85	Barb			14	cf
86	Jen			21	cg
87	John			24	ch
88	Barb			10	ci
89	Billy			26	cj
90	Jen			2	ck
91	John			20	cl
92	Barb			1	cm
93	Billy			7	cn
94	Jen			4	co
95	John			4	cp
96	Barb			19	cq
97	Jen			17	cr
98	John			6	cs
99	Barb			19	ct
100	Billy			2	cu
Source

 

[hagia_sofia]

What is happening is that the max for "Jen" is there 3 times, i.e. it returns 3 project descriptions for her. I am not sure what should happen in such a case - e.g. CONCAT these outputs?

 

[Asbestos_Jen]

Thank you for clearing that up.