Stripe out duplicate data

[Gotroots]

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)),"")

D314 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

 

[Bob Phillips]

How about

=IF(ISERROR(SMALL(IF($B$3:$B$14="no
record",ROW($B$3:$B$14)),ROW(B1))),"",INDEX($A$3:$A$14,SMALL(IF($B$3:$B$14="no
record",ROW($B$3:$B$14)),ROW(B1))-MIN(ROW($B$3:$B$14))+1))

 

[Gotroots]

I am afraid no results were returned. I am just thinking colB is confusing
matters here and should not be included in the formula. There was no
reference made to colC in your formula "C" is a key driver to what records
should be returned.

So please ignore "B" and concentrate on "A" and "C" instead.

 

[Bob Phillips]

It is an array formula, as your original was, so you need to CSE it

 

[Gotroots]

Even when array entered no results are coming back.

 

[Bob Phillips]

Well, it worked for me in my tests. Not much more I can do without the data.