using offset in combination with max formula.

[Belleke]

I have this formula

=MAX(C2:C71)

but i want combine it with an offset formula.
In a cell i have this max formula, but in the cell above i want to add the the name that belongs to the max result, these names are in column B
Thanks

 

[pecoflyer]

Perhaps

=INDEX(B2:B71,match(max(c2:c71),c2:c71,0))

 

[Belleke]

Works perfect. Thanks.

 

[Peter Bartholomew]

I have this formula

=MAX(C2:C71)

but i want combine it with an offset formula.
In a cell i have this max formula, but in the cell above i want to add the the name that belongs to the max result, these names are in column B
Thanks

The INDEX/MATCH solution is perfectly good still but Excel has moved on considerably over the past 6 years leaving many behind, stuck with obsolete code. Other formulae that work include:

= INDEX(names, XMATCH(MAX(values), values))

= XLOOKUP(MAX(values), values, names)

= XLOOKUP(MAX(values), values, names, , , {1;-1})

= FILTER(names, values=MAX(values))

One of the main differences in the results they generate is in the treatment of multiple coincident maxima.

 

[Belleke]

@Peter Bartholomew ,
Only the first formula works, but I don't get multiple coincident maxima
The other 3 give errors.

 

[bosco_yip]

Using Offset function, try:

=OFFSET(B1,MATCH(MAX(C:C),C2:C71,),)

or

=OFFSET(B1,MATCH(MAX(C:C),C:C,)-1,)

 

[Belleke]

It would be nice if I could achieve this. the treatment of multiple coincident maxima.
I use O365, but my friend works with O2021

 

[bosco_yip]

It would be nice if I could achieve this. the treatment of multiple coincident maxima.
I use O365, but my friend works with O2021

You better open a new thread and enclose with a new file, clearly stated what you have? and, what you want?

Or,
Try to see the below example for the Offset + Maxifs function:



Regards

 

[pecoflyer]

For the record OFFSET is a volatile function

 

[Peter Bartholomew]

It would be nice if I could achieve this. the treatment of multiple coincident maxima.
I use O365, but my friend works with O2021

If it is 2021 that they have, it has both XLOOKUP and FILTER. In other ways it is getting pretty out of date, but it does have the early functions associated with dynamic arrays.