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

[Ben_Millar]

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,

Ben

[#2 · Accepted solution]

@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).

[#3]

@Ben_Millar 

If you're using 365 you could use:

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

[#4]

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

[#5]

Thank you. :smiling_face_with_smiling_eyes: