Thank Robert and Biff,
Let me study your instruction carefully first, I will let your all know the
result!

Many thanks,
Wilson




Robert McCurdy wrote:
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
Thank you Biff.
The formula is working, however, the formula ONLY list "D" from cell C3 to
[quoted text clipped - 22 lines]
Many thanks,
Wilchong

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