Match to multiple max values and return associated values

ldarley

Board Regular
Joined
Apr 10, 2012
Messages
106
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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)))
 
Upvote 0
@ldarley
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 
Upvote 0
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"),""))
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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
Back
Top