How to count the matched values?

E

Eric

Does anyone have any suggestion on how to count the matched values?

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

I would like to count the number of matched value between both columns, but
I would like to set an range [+/- 1] for each values in column B. The set
value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
after the range applied to value 4, if any number is found in column A
between 3 [4-1] and 5 [4+1], then it should belong to matched value.
The matched values are 6, 4 and 9, and it will return 3 in cell C1.
Does anyone have any suggestions on how to do it?

Thanks in advance for any suggestions
Eric
 
B

Bob Phillips

Why isn't it 6, i.e.. matching 5,6,8,9,10 and 3?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
E

Eric

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

Because I would like to count the number of matched values on column B
rather than on column A.
Do you have any suggestions?
Thank you for any suggestions
Eric

Bob Phillips said:
Why isn't it 6, i.e.. matching 5,6,8,9,10 and 3?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Eric said:
Does anyone have any suggestion on how to count the matched values?

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

I would like to count the number of matched value between both columns,
but
I would like to set an range [+/- 1] for each values in column B. The set
value 1 is inputed in cell D1. Such as 4 will not be found in column A,
but
after the range applied to value 4, if any number is found in column A
between 3 [4-1] and 5 [4+1], then it should belong to matched value.
The matched values are 6, 4 and 9, and it will return 3 in cell C1.
Does anyone have any suggestions on how to do it?

Thanks in advance for any suggestions
Eric
Click to expand...
Click to expand...
 
F

Farhad

Hi,

Try this:

=MATCH(B1:B3,A1:A8)

the range can be changed
enter as an array formula: hold Ctrl and Shift and then hit Enter

Thanks,
 
F

Farhad

Hi,

Sorry i missed a part of formula:

=MATCH(B1:B3,A1:A8,1)

enter it as an array formula.

Thanks,
--
Farhad Hodjat


Farhad said:
Hi,

Try this:

=MATCH(B1:B3,A1:A8)

the range can be changed
enter as an array formula: hold Ctrl and Shift and then hit Enter

Thanks,
--
Farhad Hodjat


Eric said:
Does anyone have any suggestion on how to count the matched values?

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

I would like to count the number of matched value between both columns, but
I would like to set an range [+/- 1] for each values in column B. The set
value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
after the range applied to value 4, if any number is found in column A
between 3 [4-1] and 5 [4+1], then it should belong to matched value.
The matched values are 6, 4 and 9, and it will return 3 in cell C1.
Does anyone have any suggestions on how to do it?

Thanks in advance for any suggestions
Eric
Click to expand...
Click to expand...
 
E

Eric

Thank everyone for suggestions
Even through it returns 3 in cell C1, but it does not seem what I described
in the post.
Thank you for any suggestions
Eric

Farhad said:
Hi,

Sorry i missed a part of formula:

=MATCH(B1:B3,A1:A8,1)

enter it as an array formula.

Thanks,
--
Farhad Hodjat


Farhad said:
Hi,

Try this:

=MATCH(B1:B3,A1:A8)

the range can be changed
enter as an array formula: hold Ctrl and Shift and then hit Enter

Thanks,
--
Farhad Hodjat


Eric said:
Does anyone have any suggestion on how to count the matched values?

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

I would like to count the number of matched value between both columns, but
I would like to set an range [+/- 1] for each values in column B. The set
value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
after the range applied to value 4, if any number is found in column A
between 3 [4-1] and 5 [4+1], then it should belong to matched value.
The matched values are 6, 4 and 9, and it will return 3 in cell C1.
Does anyone have any suggestions on how to do it?

Thanks in advance for any suggestions
Eric
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Just a warning...

Eric has reposted the same question in .worksheet.functions.


Does anyone have any suggestion on how to count the matched values?

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

I would like to count the number of matched value between both columns, but
I would like to set an range [+/- 1] for each values in column B. The set
value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
after the range applied to value 4, if any number is found in column A
between 3 [4-1] and 5 [4+1], then it should belong to matched value.
The matched values are 6, 4 and 9, and it will return 3 in cell C1.
Does anyone have any suggestions on how to do it?

Thanks in advance for any suggestions
Eric
Click to expand...
 
E

Eric

Thank everyone for suggestions
Do you have any suggestions?
Eric

Dave Peterson said:
Just a warning...

Eric has reposted the same question in .worksheet.functions.


Does anyone have any suggestion on how to count the matched values?

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

I would like to count the number of matched value between both columns, but
I would like to set an range [+/- 1] for each values in column B. The set
value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
after the range applied to value 4, if any number is found in column A
between 3 [4-1] and 5 [4+1], then it should belong to matched value.
The matched values are 6, 4 and 9, and it will return 3 in cell C1.
Does anyone have any suggestions on how to do it?

Thanks in advance for any suggestions
Eric
Click to expand...
Click to expand...
 
D

Dave Peterson

Only that you shouldn't multipost your question.
Thank everyone for suggestions
Do you have any suggestions?
Eric

Dave Peterson said:
Just a warning...

Eric has reposted the same question in .worksheet.functions.


Does anyone have any suggestion on how to count the matched values?

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

I would like to count the number of matched value between both columns, but
I would like to set an range [+/- 1] for each values in column B. The set
value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
after the range applied to value 4, if any number is found in column A
between 3 [4-1] and 5 [4+1], then it should belong to matched value.
The matched values are 6, 4 and 9, and it will return 3 in cell C1.
Does anyone have any suggestions on how to do it?

Thanks in advance for any suggestions
Eric
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

Sorry for multipost question
Do you have any suggestions?
Thank everyone for any suggestions
Eric

Dave Peterson said:
Only that you shouldn't multipost your question.
Thank everyone for suggestions
Do you have any suggestions?
Eric

Dave Peterson said:
Just a warning...

Eric has reposted the same question in .worksheet.functions.



Eric wrote:

Does anyone have any suggestion on how to count the matched values?

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

I would like to count the number of matched value between both columns, but
I would like to set an range [+/- 1] for each values in column B. The set
value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
after the range applied to value 4, if any number is found in column A
between 3 [4-1] and 5 [4+1], then it should belong to matched value.
The matched values are 6, 4 and 9, and it will return 3 in cell C1.
Does anyone have any suggestions on how to do it?

Thanks in advance for any suggestions
Eric
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Only what I wrote at one of your other posts.
Sorry for multipost question
Do you have any suggestions?
Thank everyone for any suggestions
Eric

Dave Peterson said:
Only that you shouldn't multipost your question.
Thank everyone for suggestions
Do you have any suggestions?
Eric

:

Just a warning...

Eric has reposted the same question in .worksheet.functions.



Eric wrote:

Does anyone have any suggestion on how to count the matched values?

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

I would like to count the number of matched value between both columns, but
I would like to set an range [+/- 1] for each values in column B. The set
value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
after the range applied to value 4, if any number is found in column A
between 3 [4-1] and 5 [4+1], then it should belong to matched value.
The matched values are 6, 4 and 9, and it will return 3 in cell C1.
Does anyone have any suggestions on how to do it?

Thanks in advance for any suggestions
Eric
Click to expand...
Click to expand...
Click to expand...
 
Top