Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Hari Prasadh
 
Posts: n/a
Default 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


  #2   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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


  #3   Report Post  
Hari Prasadh
 
Posts: n/a
Default

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" wrote in message
...
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().



  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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" wrote in message
...

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().




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with array formula Excel Worksheet Functions 2 January 20th 05 05:17 PM
How do I use Range Names listed in a VLookup table in a formula? Essbasedvlpr32 Excel Worksheet Functions 3 December 15th 04 11:11 PM
Need help troubleshooting an array formula XLXP on Win2K KR Excel Worksheet Functions 1 December 13th 04 08:41 PM
What instead of an array formula part 2 Reg Besseling Excel Discussion (Misc queries) 2 December 10th 04 08:35 AM
Vlookup, Index & Match Phyllis Excel Worksheet Functions 1 November 8th 04 07:11 PM


All times are GMT +1. The time now is 03:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"