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

[Pramodpandit123]

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

 

[Fluff]

How about

SAMPLE DATAS.xlsx
	H		J	K	L	M	N	O		Q	R	S	T
5
6
7	Random Datas							Result
8	Direction		Axis	Distance	Speed	Ratio		Direction		Axis	Distance	Speed	Ratio
9	North		Y	9.9	9.5	1.0		North		X	7.2	7.3	1.0
10	South		X	10.2	8.8	1.2		South		X	10.2	8.8	1.2
11	East		X	8.5	7.3	1.2		East		X	8.5	7.3	1.2
12	North		X	5.8	4.9	1.2		West		X	7.1	7.2	1.0
13	West		X	2.4	2.1	1.1		North		Y	9.9	9.5	1.0
14	West		Y	10.0	9.9	1.0		South		Y	9.1	8.6	1.1
15	South		Y	9.1	8.6	1.1		East		Y	9.1	8.6	1.1
16	East		Y	9.1	8.6	1.1		West		Y	10.0	9.9	1.0
17	North		Y	7.6	7.3	1.1
18	South		X	7.5	7.3	1.0
19	East		Y	7.4	7.3	1.0
20	North		X	7.2	7.3	1.0
21	West		X	7.1	7.2	1.0
Sheet1 (2)

Cell Formulas
Range	Formula
R9:R16	R9	=MAXIFS(K:K,$H:$H,$O9,$J:$J,$Q9)
S9:T16	S9	=MAXIFS(L:L,$K:$K,$R9,$H:$H,$O9,$J:$J,$Q9)

 

[Pramodpandit123]

I don't have "MAXIFS" function availaible in Excel. Are there any alternatives to this ?

 

[Fluff]

In that case what version of Excel are you using, as 2019 has the maxifs function.

 

[Pramodpandit123]

Its Excel 2016...I have only these functions availaible when i type MAX in cell.

 

[Fluff]

Its Excel 2016

In that case why do show 2019 in your profile?

 

[Fluff]

Try

Fluff.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2
3
4
5
6
7	Random Datas							Result
8	Direction		Axis	Distance	Speed	Ratio		Direction		Axis	Distance	Speed	Ratio
9	North		Y	9.93	9.494	1.046		North		X	7.2	7.3	1.0
10	South		X	10.205	8.79	1.161		South		X	10.2	8.8	1.2
11	East		X	8.511	7.261	1.172		East		X	8.5	7.3	1.2
12	North		X	5.759	4.922	1.17		West		X	7.1	7.2	1.0
13	West		X	2.408	2.098	1.148		North		Y	9.9	9.5	1.0
14	West		Y	9.978	9.909	1.007		South		Y	9.1	8.6	1.1
15	South		Y	9.121	8.595	1.061		East		Y	9.1	8.6	1.1
16	East		Y	9.121	8.595	1.061		West		Y	10.0	9.9	1.0
17	North		Y	7.591643	7.342929	1.054393
18	South		X	7.472202	7.317357	1.043536
19	East		Y	7.352762	7.291786	1.032679
20	North		X	7.233321	7.266214	1.021821
21	West		X	7.113881	7.240643	1.010964
Input

Cell Formulas
Range	Formula
R9:R16	R9	=MAX(IF(($H$9:$H$21=$O9)*($J$9:$J$21=$Q9),$K$9:$K$21))
S9:T16	S9	=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.

 

[Pramodpandit123]

Thanks for you help ! ... Also changed the Office version in profile to 2016. (Sorry for misinformation)

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Pramodpandit123]

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 ?