You can use the same formula just change:

=0 to 0 (in 2 places)

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)0)),INDEX(rng1,SMALL(IF(COUNTI F(rng2,rng1)0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"")

Don't forget: array entered!

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:83b96cc0ca763@uwe...
Dear T. Valko,
Really thank for your help. I am thinking how to revise the existing Excel
function if I want the Excel formula extracts the data which has appear in
col B. In my early example, the result will be A, B, C, F, H, I, J,and K
actomactic appear from the cell C3 to C10 accordingly.

I need an Excel formula to have this result!

It is just the OPPOSITE what the function do for "extracting missing
data"!

Many thanks,
Wilchong




T. Valko wrote:
The "--" converts TRUE and FALSE to 1 and 0 respectively.

This expression will return an array of either TRUE or FALSE:

COUNTIF(B$3:B$13,A$3:A$14)=0

The "--" converts those either 1 or 0:

--(COUNTIF(B$3:B$13,A$3:A$14)=0)

Then the SUM function adds those 1s and 0s to get a total:

SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0))

Dear Biff,
Many thank for your explanation. Actually, another point of the Excel
[quoted text clipped - 39 lines]
Many thanks,
Wilchong

--
Message posted via http://www.officekb.com