Excel 2016 Trying to get unique values from duplicate matches

Question

I am currently running into an issue and need some help. I am using excel 2016, and in my current dataset (see sample) I am using an aggregate function to find my lowest 6 values and then I am trying to find the ID numbers of the vendors from these matches.

I tried using Index+Match, Index+Agg, and Index+match+Agg and they all failed. I am not sure how to loop through the table and find the id numbers based on just the sales reference number.

Any help is appreciated.

Answer 1

For Excel 2016 you can use following formula for ID:

=INDEX($A$1:$A$7,AGGREGATE(15,6,ROW($A$2:$A$7)/(B13=$C$2:$C$7),COUNTIF($B$13:B13,B13)))

for Name change first argument of INDEX function from $A$1:$A$7 to $B$1:$B$7