corresponding row values for max value in a column

Pecoflyer · #3 11-23-2021, 09:26 AM

The INDEX/MATCH combination can also return an entire row
Say your range is A1:C300
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
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Return as many Y as the number of occurrence of values in another column	Marcia	Excel	2	11-27-2020 04:19 PM
Formula to check combinations of values in one column to find match from another column	kong1802	Excel	1	06-15-2018 05:26 AM
SumIfs not able to get two values from the same column..	LearnerExcel	Excel	1	01-04-2017 02:32 PM
copy non zero values to new column	Guloluseus	Excel	3	01-10-2016 08:48 AM
Auto-create row from column values	ReviTULize	Excel	1	03-28-2013 02:27 AM