SOLVED

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

Copper Contributor

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

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

4 Replies
best response confirmed by Ben_Millar (Copper Contributor)
Solution

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

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

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

If you're using 365 you could use:

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

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

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

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

Thank you. :smiling_face_with_smiling_eyes:
1 best response

Accepted Solutions
best response confirmed by Ben_Millar (Copper Contributor)
Solution

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

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