The named ranges are self-explanatory.

=IF(SUM(N(COUNTIF(BB,AA)=0))<ROW(A1),"",INDIRECT(" A"&SMALL(IF(COUNTIF(BB,AA)=0,ROW(AA)),ROW(A1))) )

Put this in cell C3 using Ctrl + Shift + Enter, then fill/copy down as required.
The suggestions provided by T. Valko and this one do work, but they based on the examples you posted. If the return value is not in the first column you'll need to adjust the formula yourself - like the "A" bit for example to the letter for that column.

The reason these are different, is there are many ways to do the same thing in XL, and we do love arguing ...er, I mean discussing which one is best :)

The formula is working, however, the formula ONLY list "D" from cell C3 to
C14.
This indicates you have failed to understand how to enter array formulae (look it up), or have array entered all at once - not for this solution.

Regards
Robert McCurdy
"wilchong via OfficeKB.com" <u43231@uwe wrote in message news:837102ca9cf99@uwe...
Thank you Biff.
The formula is working, however, the formula ONLY list "D" from cell C3 to
C14.

Do you think the existing formula can be adjusted or revised to the result I
want, that is D, E, G, and L actomactic appear in cell C3, C4, C5 and C6
accordingly and C7 to C14 are the cells not display any thing.

Many thanks for your effort and time again!

Wilchong


T. Valko wrote:
Here it is with the references:

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)),INDEX(A$3:A$14,SMA LL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))),"")

Be sure to enter it as an array or it won't work properly:

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Hello Biff,
Many thanks for your help! First of all, I tried the formula but failed
[quoted text clipped - 31 lines]
Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200804/1