Microsoft Office Forums corresponding row values for max value in a column
 Register FAQ Search Today's Posts Mark Forums Read

#1
11-22-2021, 07:49 PM
 marconexcel Windows 7 64bit Office 2013 Novice Join Date: Feb 2017 Posts: 6
corresponding row values for max value in a column

I have three columns of numbers in columns A, B and C with about 300 rows. I can find the max value in A (=max(a1:a300). How do I find the corresponding row entries for columns B and C? For example, if the max value in column A is in row 157, how can I show the values in B157 and C157?
#2
11-23-2021, 05:12 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 656

You can in case the max value in column A is unique. When not, then you need some additional condition to determine, which row with e.g. 157 in column A must be returned!

When all values in column A are unique, then one possible solution:
=INDEX(\$B\$1:\$B\$300,MATCH(MAX(\$A\$1:\$A\$300),\$A\$1:\$A\$ 300,0)) to return matching entry in column B. When the max value is not unique, the data from 1st row having max value in column A is returned;
In case entry in e.g. column B is always numeric, another option will be also available:
=SUMIFS(\$B\$1:\$B\$300,\$A\$1:\$A\$300,MAX(\$A\$1:\$A\$300)) When the max value is not unique, the sum of matching values in column B is returned.
#3
11-23-2021, 09:26 AM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,524

The INDEX/MATCH combination can also return an entire row
Select a cell where you want your max value say D1
In D1 enter
Quote:
 =INDEX(\$A\$1:\$C\$300,match(max(\$A\$1:\$A\$300),\$A\$1:\$A\$ 300,0),)
( THE COMMA BEFORE THE LAST PARENTHESIS IS ESSENTIAL !)
The cell returns a VALUE error
Pull it to the right to cover 3 cells ( all now contain the error)
Go to the formula bar and commit your formula with Ctrl+Shift+Enter
Now all values from the same row including your max value are neatly in place
(the same kind of reasoning can be applied to summing (or whatever) an entire row (or column with the comma before the INDEX part) in a table)

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Marcia Excel 2 11-27-2020 04:19 PM kong1802 Excel 1 06-15-2018 05:26 AM LearnerExcel Excel 1 01-04-2017 02:32 PM Guloluseus Excel 3 01-10-2016 08:48 AM ReviTULize Excel 1 03-28-2013 02:27 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:49 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top