Match / Vlookup within an Array formula

Hi,

From cells A1 through L50 I have numbers. A particular row let's say A1:A50
might/would have some numbers repeating. Same for other rows in the range
A1:L50.

In column M from row 2 to row 10, I have some numbers (This list in column M
has no repeating numbers) . I want to do 2 kinds of calculations.

a) I want to know the count of INTERSECTION of A1:L1 and M2:M10. That is
does A1:L1 range has all the numbers present in M2:M10 or only 8 of the
numbers of M2:M10 and so on.

I used the formula =SUM(IF(ISERROR(MATCH($M$2:$M$10,A1:L1,0)),0,1))
Please note I array entered the above formula in cell N. Then I copied this
formula down to N50 so that I can know the same for each row in the range
A1:L50

Problem with the above formula is that A1:L1 would have duplicates ( and
same for other rows in the range), so am getting an incorrect answer in
using the above formula. How to weed out the duplicates and pass a unique
range of numbers within A1:L1. Or is there a better method /approach to it?
(Please note I prefer a formula /non-programmatic solution).

b) I want to know the NUMBER of ROWS within the range A1:L50 which has the
number in M2, the same for M3 and so on till M10.

So, in O2 I entered the following Array Formula to find number of rows in
the range A1:L50 which has the number in M2.

=SUM(IF(ISERROR(MATCH(M2,$A$1:$L$50,0)),0,1))

Im getting a Zero. I went in to Evaluate formula mode (Have 2002) but ...

I know that the Look_up array within the Match function has to be a One Row
/ Column Range.. but doesnt using an Array formula mean that Excel will
break the range A1:L50 in the Lookup_array in to single row ranges and then
do such evaluations for each row. Isnt an Array Formula is supposed to do
that kind of things?

Please guide me in resolving b) as well.

Thanks a lot,
Hari
India

a]

N1, copied down:

=SUMPRODUCT((A1:L1<>"")/COUNTIF(A1:L1,A1:L1&""),--ISNUMBER(MATCH(A1:L1,$M$2:$M$10,0)))

b]

O2, copied to O9...

=SUMPRODUCT(--(COUNTIF(INDIRECT(ADDRESS(ROW($A$1:$A$50),COLUMN(A$1))&":"&ADDRESS(ROW($A$1:$A$50),COLUMNS(A$1:L$1))),M2)>=1))

Investigate whether you can replace INDIRECT(ADDRESS(...),ADDRESS(...))
with OFFSET().

Hari Prasadh wrote:
> Hi,
>
> From cells A1 through L50 I have numbers. A particular row let's say A1:A50
> might/would have some numbers repeating. Same for other rows in the range
> A1:L50.
>
> In column M from row 2 to row 10, I have some numbers (This list in column M
> has no repeating numbers) . I want to do 2 kinds of calculations.
>
> a) I want to know the count of INTERSECTION of A1:L1 and M2:M10. That is
> does A1:L1 range has all the numbers present in M2:M10 or only 8 of the
> numbers of M2:M10 and so on.
>
> I used the formula =SUM(IF(ISERROR(MATCH($M$2:$M$10,A1:L1,0)),0,1))
> Please note I array entered the above formula in cell N. Then I copied this
> formula down to N50 so that I can know the same for each row in the range
> A1:L50
>
> Problem with the above formula is that A1:L1 would have duplicates ( and
> same for other rows in the range), so am getting an incorrect answer in
> using the above formula. How to weed out the duplicates and pass a unique
> range of numbers within A1:L1. Or is there a better method /approach to it?
> (Please note I prefer a formula /non-programmatic solution).
>
> b) I want to know the NUMBER of ROWS within the range A1:L50 which has the
> number in M2, the same for M3 and so on till M10.
>
> So, in O2 I entered the following Array Formula to find number of rows in
> the range A1:L50 which has the number in M2.
>
> =SUM(IF(ISERROR(MATCH(M2,$A$1:$L$50,0)),0,1))
>
> Im getting a Zero. I went in to Evaluate formula mode (Have 2002) but ...
>
> I know that the Look_up array within the Match function has to be a One Row
> / Column Range.. but doesnt using an Array formula mean that Excel will
> break the range A1:L50 in the Lookup_array in to single row ranges and then
> do such evaluations for each row. Isnt an Array Formula is supposed to do
> that kind of things?
>
> Please guide me in resolving b) as well.
>
> Thanks a lot,
> Hari
> India
>
>

Hi Aladin,

Your formula works nicely for both a) and b). Thnx a lot.

I have a doubt. How is it that in a) Match has a range (not a cell) for
Lookup_value but still we dont need to Array enter the formula for getting
correct answers.

Thanks a lot,
Hari
India


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> a]
>
> N1, copied down:
>
> =SUMPRODUCT((A1:L1<>"")/COUNTIF(A1:L1,A1:L1&""),--ISNUMBER(MATCH(A1:L1,$M$2:$M$10,0)))
>
> b]
>
> O2, copied to O9...
>
> =SUMPRODUCT(--(COUNTIF(INDIRECT(ADDRESS(ROW($A$1:$A$50),COLUMN(A$1))&":"&ADDRESS(ROW($A$1:$A$50),COLUMNS(A$1:L$1))),M2)>=1))
>
> Investigate whether you can replace INDIRECT(ADDRESS(...),ADDRESS(...))
> with OFFSET().
>

MATCH is capable of returning an array of results. That we don't need
control+shift+enter is SumProduct's doing.

Hari Prasadh wrote:
> Hi Aladin,
>
> Your formula works nicely for both a) and b). Thnx a lot.
>
> I have a doubt. How is it that in a) Match has a range (not a cell) for
> Lookup_value but still we dont need to Array enter the formula for getting
> correct answers.
>
> Thanks a lot,
> Hari
> India
>
>
> "Aladin Akyurek" <[email protected]> wrote in message
> news:[email protected]...
>
>>a]
>>
>>N1, copied down:
>>
>>=SUMPRODUCT((A1:L1<>"")/COUNTIF(A1:L1,A1:L1&""),--ISNUMBER(MATCH(A1:L1,$M$2:$M$10,0)))
>>
>>b]
>>
>>O2, copied to O9...
>>
>>=SUMPRODUCT(--(COUNTIF(INDIRECT(ADDRESS(ROW($A$1:$A$50),COLUMN(A$1))&":"&ADDRESS(ROW($A$1:$A$50),COLUMNS(A$1:L$1))),M2)>=1))
>>
>>Investigate whether you can replace INDIRECT(ADDRESS(...),ADDRESS(...))
>>with OFFSET().
>>
>
>
>