Finding out the name of the most expensive item in the product group

[Dsuminas]

Dear members,

I am far away from excel expert and probably can't even properly describe what I really need in the title of this thread
Recently I got a new job where I have to work a lot with excel. My colleagues are already working for years in the same position as I just started and I suspect that they haven't exploited excel opportunities in full. I'm a little bit lazy and get quickly tired of monotonuous clicking for hours. Maybe someone in this forum can help me answer the following?
Here I made a visual example:
Fiksavimas.JPG

I have articles that belong to article groups. In the column D I need a formula, which I can drag-down to the bottom and get a name of the most expensive article in the group. Does someone have an idea how to do this? I already tried so many times by myself, called all my friends and none of them could help me.

In reality I have 800 different groups and around 17 000 diferent article names. Me and my colleagues would appreciate if someone could help us with this formula

Thanks in advance!

[protonLeah]

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[Dsuminas]

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Thanks Ben! It works almost perfect :D
I had to replace commas into ";" in your formula in order to make it work. And I don't know if I ruined your formula by replacing commas, or this formula is not designed to put results in every single cell? Now if I drag the formula all the way to the bottom, then it gives results that are true, but there are blank spaces in between. Is it possible to reajust the formula in a way that it fills every single cell in a column?

Formula I put in D2 without commas (with ctrl+shift+enter)

=IFERROR(INDEX($B$2:$B$30;MATCH(IF(COUNTIF($A$2:$A2;$A2)=1;MAX(IF($A$2:$A$30=A2;$C$2:$C$30));"");$C$2:$C$30;0));"")

Fiksavimas2.JPG

[Glenn Kennedy]

One way (non-array):

You may need ; instead of ,

=INDEX($B$2:$B$35,MATCH(1,INDEX(($A$2:$A$35=A2)*($C$2:$C$35=AGGREGATE(14,6,$C$2:$C$35/($A$2:$A$35=A2),1)),0),0))

[Dsuminas]

Thanks Glenn! That's exactly what I was looking for!!!
You just saved me at least one hour of every working day!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.