How to find the maximum value among unique distinct data's and Return adjacent column data respective to the maximum value ?

Pramodpandit123

New Member
Joined
Apr 18, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
Hello Everyone,
How to find the maximum value among unique distinct datas from Distance column and Return Speed column data respective to the maximum value.
For eg: The maximum value of " North in X Axis" is 7.2 among random datas and the Speed column respective to the maximum i.e 7.2 is 7.3.

As VLOOKUP only uses single criteria but in this situation there is multiple criteria i.e Lookup value of North direction and X Axis. What are the possible solutions that we could achieve this ?

P.S : Link to sample Excel file: Loading Google Sheets
 

Attachments

  • Capture.JPG
    Capture.JPG
    75.6 KB · Views: 9

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
How about
SAMPLE DATAS.xlsx
HJKLMNOQRST
5
6
7Random DatasResult
8DirectionAxisDistanceSpeedRatioDirectionAxisDistanceSpeedRatio
9NorthY9.99.51.0NorthX7.27.31.0
10SouthX10.28.81.2SouthX10.28.81.2
11EastX8.57.31.2EastX8.57.31.2
12NorthX5.84.91.2WestX7.17.21.0
13WestX2.42.11.1NorthY9.99.51.0
14WestY10.09.91.0SouthY9.18.61.1
15SouthY9.18.61.1EastY9.18.61.1
16EastY9.18.61.1WestY10.09.91.0
17NorthY7.67.31.1
18SouthX7.57.31.0
19EastY7.47.31.0
20NorthX7.27.31.0
21WestX7.17.21.0
Sheet1 (2)
Cell Formulas
RangeFormula
R9:R16R9=MAXIFS(K:K,$H:$H,$O9,$J:$J,$Q9)
S9:T16S9=MAXIFS(L:L,$K:$K,$R9,$H:$H,$O9,$J:$J,$Q9)
 
Upvote 0
I don't have "MAXIFS" function availaible in Excel. Are there any alternatives to this ?
 
Upvote 0
In that case what version of Excel are you using, as 2019 has the maxifs function.
 
Upvote 0
Its Excel 2016...I have only these functions availaible when i type MAX in cell.
 

Attachments

  • Capture.JPG
    Capture.JPG
    12.8 KB · Views: 3
Upvote 0
Try
Fluff.xlsm
HIJKLMNOPQRST
1
2
3
4
5
6
7Random DatasResult
8DirectionAxisDistanceSpeedRatioDirectionAxisDistanceSpeedRatio
9NorthY9.939.4941.046NorthX7.27.31.0
10SouthX10.2058.791.161SouthX10.28.81.2
11EastX8.5117.2611.172EastX8.57.31.2
12NorthX5.7594.9221.17WestX7.17.21.0
13WestX2.4082.0981.148NorthY9.99.51.0
14WestY9.9789.9091.007SouthY9.18.61.1
15SouthY9.1218.5951.061EastY9.18.61.1
16EastY9.1218.5951.061WestY10.09.91.0
17NorthY7.5916437.3429291.054393
18SouthX7.4722027.3173571.043536
19EastY7.3527627.2917861.032679
20NorthX7.2333217.2662141.021821
21WestX7.1138817.2406431.010964
Input
Cell Formulas
RangeFormula
R9:R16R9=MAX(IF(($H$9:$H$21=$O9)*($J$9:$J$21=$Q9),$K$9:$K$21))
S9:T16S9=MAX(IF(($H$9:$H$21=$O9)*($J$9:$J$21=$Q9)*($K$9:$K$21=$R9),L$9:L$21))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution
Thanks for you help ! ... Also changed the Office version in profile to 2016. (Sorry for misinformation)
 
Upvote 0
Just a quick question: Using the same formula, it doesn't seem to return the Strings/Texts as the formula would return "0" only. How could we return Text values using above formulas ?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top