Match to multiple max values and return associated values

ldarley

Board Regular
Joined
Apr 10, 2012
Messages
104
Office Version
  1. 2019
  2. 2016
Hi I have a series of data points set out in two rows the first row is a date the second row is the values. I want to pull some summary stats about the series together e.g. max, min, average etc. standard stuff.

But I also want to return the date when the max value occurred, again standard index match stuff if there is only one max value, but looking at the data it could well be that I have more than one cell in the series that is at the max value.

I can't for the life of me work out how I would do this - but in instances where there is more than one item at the max value then I would like to match to that value and then return all the dates associated with that max value all concatenated in one cell.

Data looks something like this:

Jan 21Feb 21Mar 21Apr 21May 21Jun 21Jul 21
1010141614816

So in this instance the formula would return

Apr 21, Jul 21

This is something I would be a repeated task with new data dropped into the series each time I wanted to pull the summary stats together. Any pointers in how to achieve this wold be gratefully recieved!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,373
Office Version
  1. 2016
Platform
  1. Windows
Hi Idarley,

I can pull into adjacent columns but I can't see how to concatenate unless I use worker columns up to the maximum duplicated MAX values.

Idarley.xlsx
CDEFGHI
1Jan 21Feb 21Mar 21Apr 21May 21Jun 21Jul 21
21010141614816
3
4Apr 21Jul 21
Sheet1
Cell Formulas
RangeFormula
C4:D4C4=INDEX($C$1:$I$1,AGGREGATE(15,6,COLUMN($C$1:$I$1)-COLUMN($B$1)/($C$2:$I$2=MAX($C$2:$I$2)),COLUMNS($C$1:C1)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
@ldarley
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
If you have the TextJoin function.
+Fluff v2.xlsm
ABCDEFG
101/01/202101/02/202101/03/202101/04/202101/05/202101/06/202101/07/2021
21010141614816
3
4
5Apr 21,Jul 21
Main
Cell Formulas
RangeFormula
B5B5=TEXTJOIN(",",,IF(A2:G2=MAX(A2:G2),TEXT(A1:G1,"mmm yy"),""))
 
Solution

ldarley

Board Regular
Joined
Apr 10, 2012
Messages
104
Office Version
  1. 2019
  2. 2016
If you have the TextJoin function.
+Fluff v2.xlsm
ABCDEFG
101/01/202101/02/202101/03/202101/04/202101/05/202101/06/202101/07/2021
21010141614816
3
4
5Apr 21,Jul 21
Main
Cell Formulas
RangeFormula
B5B5=TEXTJOIN(",",,IF(A2:G2=MAX(A2:G2),TEXT(A1:G1,"mmm yy"),""))
Hi Fluff account updated I've got 2016 and 2019 on different laptops so yes the text join formula is there. Your solution works perfectly thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,780
Members
415,927
Latest member
vedasinternational

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
Top