Lookup Max value formula

[naseerrahaman]

Hi Team,

I am looking for the formula where if lookup value matches the lookuparray then formula should pick the max value for the match and return corresponding value.

for better understanding please refer the attached excel file.

Max value lookup.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Lookup value		Vookup Array				Matched	Result from column E	Based on Max Value
2	Rehaman		Rehaman	20	A		Rehaman	B	90
3	Naseer		Rehaman	90	B		Naseer	H	100
4			Rehaman	80	C
5			Rehaman	30	D
6			Naseer	1	E
7			Naseer	2	F
8			Naseer	8	G
9			Naseer	100	H
10			Naseer	67	I
11			Xyz	9	J
12			Xyz	8	K
13			Xyz	10	L
Sheet1

Note: cross posted here

I request you to kindly look into this and advise.

Thank you.

Regards,
Rehaman

 

[earthworm]

Try this

Book1
	A	B	C	D	E	F	G	H	I
1	Lookup value		Vookup Array				Matched	Result from column E	Based on Max Value
2	Rehaman		Rehaman	20	A		Rehaman	B	90
3	Naseer		Rehaman	90	B		Naseer	H	100
4			Rehaman	80	C
5			Rehaman	30	D
6			Naseer	1	E
7			Naseer	2	F
8			Naseer	8	G
9			Naseer	100	H
10			Naseer	67	I
11			Xyz	9	J
12			Xyz	8	K
13			Xyz	10	L
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=INDEX($E$2:$E$13,MATCH(MAXIFS($D$2:$D$13,$C$2:$C$13,G2),$D$2:$D$13,0),1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[naseerrahaman]

Try this

Book1
	A	B	C	D	E	F	G	H	I
1	Lookup value		Vookup Array				Matched	Result from column E	Based on Max Value
2	Rehaman		Rehaman	20	A		Rehaman	B	90
3	Naseer		Rehaman	90	B		Naseer	H	100
4			Rehaman	80	C
5			Rehaman	30	D
6			Naseer	1	E
7			Naseer	2	F
8			Naseer	8	G
9			Naseer	100	H
10			Naseer	67	I
11			Xyz	9	J
12			Xyz	8	K
13			Xyz	10	L
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=INDEX($E$2:$E$13,MATCH(MAXIFS($D$2:$D$13,$C$2:$C$13,G2),$D$2:$D$13,0),1)

Hi earthworm,​

Formula working fine and thank you so much for all your help and support.

Regards,
Rehaman.

 

[naseerrahaman]

Try this

Book1
	A	B	C	D	E	F	G	H	I
1	Lookup value		Vookup Array				Matched	Result from column E	Based on Max Value
2	Rehaman		Rehaman	20	A		Rehaman	B	90
3	Naseer		Rehaman	90	B		Naseer	H	100
4			Rehaman	80	C
5			Rehaman	30	D
6			Naseer	1	E
7			Naseer	2	F
8			Naseer	8	G
9			Naseer	100	H
10			Naseer	67	I
11			Xyz	9	J
12			Xyz	8	K
13			Xyz	10	L
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=INDEX($E$2:$E$13,MATCH(MAXIFS($D$2:$D$13,$C$2:$C$13,G2),$D$2:$D$13,0),1)

Hi Earthworm,

Is it possible to achieve the same result without using the Maxifs as in other version maxifs is not available.

Please advise.

Thank you.

Regards,
Rehaman.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Lookup value		Vookup Array				Matched	Result from column E	Based on Max Value
2	Rehaman		Rehaman	20	A		Rehaman	B	90
3	Naseer		Rehaman	90	B		Naseer	H	100
4			Rehaman	80	C
5			Rehaman	30	D
6			Naseer	1	E
7			Naseer	2	F
8			Naseer	8	G
9			Naseer	100	H
10			Naseer	67	I
11			Xyz	9	J
12			Xyz	8	K
13			Xyz	10	L
Summary

Cell Formulas
Range	Formula
H2:H3	H2	=INDEX($E$2:$E$13,MATCH(1,($C$2:$C$13=G2)*($D$2:$D$13=I2),0))
I2:I3	I2	=AGGREGATE(14,6,$D$2:$D$13/($C$2:$C$13=G2),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[naseerrahaman]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Lookup value		Vookup Array				Matched	Result from column E	Based on Max Value
2	Rehaman		Rehaman	20	A		Rehaman	B	90
3	Naseer		Rehaman	90	B		Naseer	H	100
4			Rehaman	80	C
5			Rehaman	30	D
6			Naseer	1	E
7			Naseer	2	F
8			Naseer	8	G
9			Naseer	100	H
10			Naseer	67	I
11			Xyz	9	J
12			Xyz	8	K
13			Xyz	10	L
Summary

Cell Formulas
Range	Formula
H2:H3	H2	=INDEX($E$2:$E$13,MATCH(1,($C$2:$C$13=G2)*($D$2:$D$13=I2),0))
I2:I3	I2	=AGGREGATE(14,6,$D$2:$D$13/($C$2:$C$13=G2),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi Earthworm,

Excellent, it's working fine.

Thank you so much for all your help and support.

Regards,
Rehaman.

 

[Fluff]

I'm Fluff, not earthworm.

Glad to help & thanks for the feedback.

 

[naseerrahaman]

Extremely sorry Fluff and once again thank you so much for all your help and support.

 

[Fluff]

Extremely sorry Fluff

No worries.