Offset formula

[Mel Smith]

Hello folks,

I'm trying to use Offset in my formula but I'm getting nowhere. Your advice will be most appreciated.

In cell E33 I have: =max(E8:E31)
In cell D33 I want to show the corresponding name (in A8:A31) to the maximum value shown in E33. It is possible that there might be more than one name to display. How might I achieve this?

Mel

 

[GraH]

Maybe like this?

Book1
	A	B	C	D	E	F
8	Name1	36
9	Name2	24
10	Name3	16
11	Name4	41
12	Name5	9
13	Name6	48
14	Name7	18
15	Name8	7
16	Name9	39
17	Name10	45
18	Name11	44
19	Name12	12
20	Name13	48
21	Name14	18
22	Name15	46
23	Name16	30
24	Name17	22
25	Name18	36
26	Name19	39
27	Name20	14
28	Name21	20
29	Name22	13
30	Name23	19
31	Name24	48
32
33					48	Name6;Name13;Name24
Sheet3

Cell Formulas
Range	Formula
E33	E33	=MAX(B8:B31)
F33	F33	=TEXTJOIN(";",TRUE,FILTER(A8:A31,B8:B31=E33,))

 

[Mel Smith]

Perfect! Thank you.

Mel

 

[GraH]

You're most welcome, Mel.
Just wondering, how did you manage to mark your own post as a solution?

 

[Mel Smith]

I ticked the wrong post in my excitement!

 

[GraH]

I overlook a zillion things that way ?

 

[Joe4]

Just wondering, how did you manage to mark your own post as a solution?

I have corrected it so that the correct reply now appears as the solution.

 

[Mel Smith]

Thank you!