The following data gives an explanation of what I want the formula in €œD€� to
return.

A3:A14 are entered values

A3 = apples
A4 = apples
A5 = pears
A6 = oranges
A7 = apples
A8 = grapefruit
A9 = grapes
A10 = lemons
A11 = bananas
A12 = bananas
A13 = kiwi
A14 = plums

B3:B14 are the result of a formula

B3 = no record
B4 = no record
B5 =
B6 =
B7 = no record
B8 =
B9 = no record
B10 = no record
B11 = no record
B12 = no record
B13 =
B14 = no record

C3:C14 are the result of a formula

C3 = 3
C4 = 4
C5 =
C6 =
C7 = 7
C8 =
C9 = 9
C10 = 10
C11 = 11
C12 = 12
C13 =
C14 = 14

D3 contains the formula;
=IF(COUNTIF($A$3:$A42,A4)=1,INDEX(INDIRECT("$A$3:$ A$5000"),MATCH(SMALL($C$3:$C$5000,ROWS($IV$1:IV1)) ,$C$3:$C$5000,0)),"")

D3:D14 contains the result of the above formula relative to each cell

D3 =
D4 = apples
D5 = apples
D6 =
D7 = lemons
D8 = bananas
D9 = bananas
D10 =
D11 =
D12 = #NUM!
D13 = #NUM!
D14 =

What the formula should have returned is as follows:

D3 = apples
D4 = grapes
D5 = lemons
D6 = bananas
D7 = plums
D8 =
D9 =
D10 =
D11 =
D12 =
D13 =
D14 =

Hope someone can tell me what is wrong with the formula in €œD€�
Thank you