Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-22-2021, 07:49 PM
marconexcel marconexcel is offline corresponding row values for max value in a column Windows 7 64bit corresponding row values for max value in a column Office 2013
Novice
corresponding row values for max value in a column
 
Join Date: Feb 2017
Posts: 6
marconexcel is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 11-23-2021, 05:12 AM
ArviLaanemets ArviLaanemets is offline corresponding row values for max value in a column Windows 8 corresponding row values for max value in a column Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
  #3  
Old 11-23-2021, 09:26 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline corresponding row values for max value in a column Windows 7 64bit corresponding row values for max value in a column Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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)
Reply With Quote
Reply

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
corresponding row values for max value in a column 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
corresponding row values for max value in a column Auto-create row from column values ReviTULize Excel 1 03-28-2013 02:27 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft