Finding maximum value against a value using vlookup

[kalucharan]

Could anyone help me with the formula for below problem.
I need to find the item name with the maximum price in a new column against the name.
Attached the excel for the reference.

Finding maximum.xlsx
	C	D	E	F	G
1	Name	Item Name	Price	Expected Result	Formula to be used using vlookup
2	Harry	A	20	C
3		B	67
4		C	87
5		D	87
6	Tom	A1	25	E1
7		B1	69
8		C1	78
9		D1	79
10		E1	87
Sheet1

 

[JvdV]

Here is one way, but it is ms365. I'm unsure about your version of Excel as it's not included in your profile.

Book1
	A	B	C	D
1	Name	Item Name	Price	Expected Result
2	Harry	A	20	C
3		B	67
4		C	87
5		D	87
6	Tom	A1	25	E1
7		B1	69
8		C1	78
9		D1	79
10		E1	87
Sheet9

Cell Formulas
Range	Formula
D2:D10	D2	=LET(x,SCAN(A2,A2:A10,LAMBDA(a,b,IF(b="",a,b))),MAP(A2:A10,LAMBDA(z,IF(z<>"",@SORT(FILTER(B2:C10,x=z),2,-1),""))))
Dynamic array formulas.

 

[kalucharan]

Here is one way, but it is ms365. I'm unsure about your version of Excel as it's not included in your profile.

Book1
	A	B	C	D
1	Name	Item Name	Price	Expected Result
2	Harry	A	20	C
3		B	67
4		C	87
5		D	87
6	Tom	A1	25	E1
7		B1	69
8		C1	78
9		D1	79
10		E1	87
Sheet9

Cell Formulas
Range	Formula
D2:D10	D2	=LET(x,SCAN(A2,A2:A10,LAMBDA(a,b,IF(b="",a,b))),MAP(A2:A10,LAMBDA(z,IF(z<>"",@SORT(FILTER(B2:C10,x=z),2,-1),""))))
Dynamic array formulas.

I am using MS 2016. Could you please write formula using vlookup.

 

[Fluff]

I am using MS 2016

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’)

 

[Fluff]

Duplicate to: Finding maximum against a value

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.