Want to return unique value for each duplicate

[Sean15]

Excel 2010
	C	D	E
3	John Paul		12.50
4	John Paul		10.50
5	John Paul		11.50
6	John Paul		9.50
7	John Paul		8.75
8	John Paul		4.50
9	Mike		15.75
10	John C Paul		7.50
11
12
13
14	John Paul		12.50
15	John Paul		10.50
16	John C Paul		9.50
17	John Paul		8.75
18	John Paul		4.50
19	John Paul		#NUM!
20	Mike		15.75
single lookup value

Cell Formulas
Range	Formula
E14	{=INDEX($E$3:$E$10,SMALL(IF($C$14:$C$21=C3,ROW($E$3:$E$10)-ROW($E$3)+1),COUNTIFS($C$3:$C3,$C3)))}
E15	{=INDEX($E$3:$E$10,SMALL(IF($C$14:$C$21=C4,ROW($E$3:$E$10)-ROW($E$3)+1),COUNTIFS($C$3:$C4,$C4)))}
E16	{=INDEX($E$3:$E$10,SMALL(IF($C$14:$C$21=C5,ROW($E$3:$E$10)-ROW($E$3)+1),COUNTIFS($C$3:$C5,$C5)))}
E17	{=INDEX($E$3:$E$10,SMALL(IF($C$14:$C$21=C6,ROW($E$3:$E$10)-ROW($E$3)+1),COUNTIFS($C$3:$C6,$C6)))}
E18	{=INDEX($E$3:$E$10,SMALL(IF($C$14:$C$21=C7,ROW($E$3:$E$10)-ROW($E$3)+1),COUNTIFS($C$3:$C7,$C7)))}
E19	{=INDEX($E$3:$E$10,SMALL(IF($C$14:$C$21=C8,ROW($E$3:$E$10)-ROW($E$3)+1),COUNTIFS($C$3:$C8,$C8)))}
E20	{=INDEX($E$3:$E$10,SMALL(IF($C$14:$C$21=C9,ROW($E$3:$E$10)-ROW($E$3)+1),COUNTIFS($C$3:$C9,$C9)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

This Array with Control, Shift and Enter returns required value except for incorrect value in E16 and #NUM! error in E19. I prefer using a formula without CSE but I haven’t had much luck finding a solution online. Could someone post formula that does not require CSE? Formula also needs to case sensitive to take care of errors in E16 and E19. Thank you very much for your help.

 

[Aladin Akyurek]

In E14 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($E$3:$E$10,SMALL(IF($C$3:$C$10=$C14,ROW($E$3:$E$10)-ROW($E$3)+1),COUNTIFS($C$14:C14,C14))),"")

or just enter and copy down:

=IFERROR(INDEX($E$3:$E$10,AGGREGATE(15,6,(ROW($E$3:$E$10)-ROW($E$3)+1)/($C$3:$C$10=$C14),COUNTIFS($C$14:C14,C14))),"")

 

[Sean15]

or just enter and copy down:

=IFERROR(INDEX($E$3:$E$10,AGGREGATE(15,6,(ROW($E$3:$E$10)-ROW($E$3)+1)/($C$3:$C$10=$C14),COUNTIFS($C$14:C14,C14))),"")

Outstanding. Thank you very much.

Is it advisable to replace VLOOKUP formulas with the one shown above (format to suit)?

 

[Aladin Akyurek]

Outstanding. Thank you very much.

You are welcome.

Is it advisable to replace VLOOKUP formulas with the one shown above?

No, I don't think so. By the way, it's possible to set up fast running look up formulas if the table can be sorted on its match range in ascending order and kept sorted that way.