Excel Max function how to print the row of data that the max value is on

Copper Contributor

I have 3 columns of data and I am trying to figure out how to print the row that has the maximum value in column 3.


Column 1 has cause, column 2 has year it happened and column 3 has the frequency it happened/amount. I want the row that has the greatest values in column 3 to be printed on another part of the page with the cause and year, and if I change any data in the 3 columns for it to be changed in the other part that says the maximum.


Hoping someone can help,


4 Replies
best response confirmed by Ben_Millar (Copper Contributor)

@Ben_Millar Attached a file with two possible solutions. See which one works for you. One uses the dynamic array function FILTER. The other INDEX/MATCH entered with Ctrl-Shift-Enter (array formula).




If you're using 365 you could use:


=LET(sorted, SORT(Table1, 3, -1), TAKE(sorted, 1))


Thank you that seams to have sorted it out. :smiling_face_with_smiling_eyes:
Thank you. :smiling_face_with_smiling_eyes: