Finding maximum against a value

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.

Ideally, you want to have the names repeating down column C.

We can force this in a few ways. You can start with putting the formula =C2 in cell C3 then copying cell C3 and pasting it in all blank cells of column C.
You can use Find & Select > Go to Special to select all blank cells in column C before pasting.

Now that your data is clean, you can put this in G2:
=INDEX(D$2:D$10,MATCH(C2&MAX(IF(C$2:C$10=C2,E$2:E$10)),$C$2:$C$10&E$2:E$10,0)) Ctrl Shift Enter for older versions of Excel

Drag the formula down column G

I think you made the formula bit complex. Can we have vlookup formula and find the maximum value.

Originally Posted by 63falcondude

Ideally, you want to have the names repeating down column C.

We can force this in a few ways. You can start with putting the formula =C2 in cell C3 then copying cell C3 and pasting it in all blank cells of column C.
You can use Find & Select > Go to Special to select all blank cells in column C before pasting.

Now that your data is clean, you can put this in G2:
=INDEX(D$2:D$10,MATCH(C2&MAX(IF(C$2:C$10=C2,E$2:E$10)),$C$2:$C$10&E$2:E$10,0)) Ctrl Shift Enter for older versions of Excel

Drag the formula down column G

Can you vlookup and find the maximum value as per the column Expected Result

VLOOKUP returns the first match, not the maximum value.