#1
|
|||
|
|||
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
|
|||
|
|||
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
|
||||
|
||||
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:
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 | |
|
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 |