I have a question. I'm collecting social media statistics (likes, shares, comments, etc.), and one thing that I'm trying to do is get Excel to tell me the date that the largest number of hits occurred, what post it was on, and what type it was.
I'm part of the way there--I can get Excel to tell me what the highest number is (MAX, easy!), but I can't get it to lock in the date properly.
The way I've got it set up is: there's a single row for each day of the month, with the dates in column A, and additional columns for the types of engagements I'm tracking. At the very bottom of the table is a row with all the MAX values from each column (so in other words, column B will have Likes, column C is shares, and so on, so I can enter a new value for each analytic every day.
Essentially, what I'm trying to do is get Excel to do this, in order:
- Look along the row with all the MAX values to find the highest value;
- Look up above that (same column) into the data rows until it finds that MAX value;
- Take note of the row where it found that MAX value, and return the value of the date from Column A
Make sense? I've got a formula set up with INDEX and MATCH, but while it is giving me the correct numbers for the top value and what type of analytic it is, I can't seem to get the correct date (what it looks like it's doing is counting the columns from left until it finds the value for #2, then counting that number of rows down).
Can anyone help?
CodePudding user response：
You can do this by nesting MAX, MATCH and INDEX
Mockup of your data
The Formula
=INDEX(Table1[Date], MATCH(MAX(Table1[#Totals]), INDEX(Table1, , MATCH(MAX(Table1[#Totals]), Table1[#Totals], 0) ), 0 ) )
Explanation of the Formula
Of course, you don't have to use a Table. A formula for the same data, without Structured References
=INDEX(Sheet1!$K$2:$K$10, MATCH(MAX(Sheet1!$K$11:$P$11), INDEX(Sheet1!$K$2:$P$10, , MATCH(MAX(Sheet1!$K$11:$P$11), Sheet1!$K$11:$P$11, 0) ), 0 ) )