Thank eveyone very much for suggestions
Eric

"Ron Rosenfeld" wrote:

On Sat, 25 Aug 2007 06:20:01 -0700, Eric
wrote:

Thank everyone for suggestions

I would like to further provide addition conditions for this matching.

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 4,6,1

After knowing how to valid the values in cell B1, I get more values to be
valided in column B, in this example, there are 4,6,1 in column B, and I
would like to know how many values in column B are valided to be matched with
any value in column A.

The values in column B are 4,6,1, which is valided based on given condition.
There are 3 matched values and return 3 in cell E1.

Does anyone have any suggestions on how to determine the number of matched
values in column B?

Thank everyone very much for any suggesitons
Eric


Adapting my formula -- **array-entered** with <ctrl<shift<enter:

=OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B1-D1,1)&":"&B1+D1)),A1:A8,0)))+
OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B2-D1,1)&":"&B2+D1)),A1:A8,0)))+
OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B3-D1,1)&":"&B3+D1)),A1:A8,0)))

Adapting Gary's student's formula (probably better):

=(SUMPRODUCT(--(A1:A8=B1-D1),--(A1:A8<=B1+D1))0)+
(SUMPRODUCT(--(A1:A8=B2-D1),--(A1:A8<=B2+D1))0)+
(SUMPRODUCT(--(A1:A8=B3-D1),--(A1:A8<=B3+D1))0)
--ron