Double values Index Match formulas

[TimvMechelen]

To Explain my problem, I have an example file:

Excel file:
https://www.dropbox.com/s/szmitcdo4q3qob5/example.xlsx?dl=0

In column G I have an INDEX Match formula. But it returns double values. In Column H it shows the values the formula should return.

Can somebody help me with a formula?

Thanks for the help

 

[Jonmo1]

Yep, that's the expected behavior. Match finds the first occurance of the lookup value.

Why don't you just actually sort the original data (columns B and C), using C Ascending ?

 

[steve the fish]

You could try this array formula:

=INDEX($B$2:$B$23,SMALL(IF($C$2:$C$23=F2,ROW($C$2:$C$23)-ROW($C$2)+1),COUNTIF($F$2:F2,F2)))

Enter with CTRL-SHIFT-ENTER not just ENTER.

 

[TimvMechelen]

You could try this array formula:

=INDEX($B$2:$B$23,SMALL(IF($C$2:$C$23=F2,ROW($C$2:$C$23)-ROW($C$2)+1),COUNTIF($F$2:F2,F2)))

Enter with CTRL-SHIFT-ENTER not just ENTER.

Thanks! it worked!