"IN Operator" using Match function....
 

I am using the Match function to operate similar to a sql IN operator,
but I can't find anything that tells me what I am doing is okay. My
test results have been correct, but I will be applying this formula to
millions of dollars of calculations and don't want to get my figures
off.

What I am doing is basically this. I have column A containing usps
two character mailing codes. Column B contains a number. I want to
sum the values in column B where the value in column A is a state in
the US thereby eliminating any rows where column A is a country code.
I have a list of all the US state codes in column J. So I want to sum
column B if the value in column A exists in the list in column J.

Here is my simple example and formula.

A B ... J
WI 1 AL
NZ 1 AK
IL 1 AS
CA 1 AZ
EN 1 AR


The forumula I am using is as follows
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,J2:J60, 0))), B1:B5).

It appears to be giving correct results (WI, IL and CA are being
summed with england and new zealand left out), however, the MATCH
function as documented is not supposed to take a range as the first
parameter, only a value.

Is this valid, or not, or is there a better way to do this?

Thanks in advance for the help.

Andy

"IN Operator" using Match function....
 

Is this valid, or not

Yes it is.

is there a better way to do this?

That formula is about as efficient as it gets for the application.

the MATCH function as documented is not
supposed to take a range as the first parameter

The help documentation is quite lacking (IMHO). You don't see anything in
there about using the double unary minus with SUMPRODUCT, do you?


--
Biff
Microsoft Excel MVP


wrote in message
...
I am using the Match function to operate similar to a sql IN operator,
but I can't find anything that tells me what I am doing is okay. My
test results have been correct, but I will be applying this formula to
millions of dollars of calculations and don't want to get my figures
off.

What I am doing is basically this. I have column A containing usps
two character mailing codes. Column B contains a number. I want to
sum the values in column B where the value in column A is a state in
the US thereby eliminating any rows where column A is a country code.
I have a list of all the US state codes in column J. So I want to sum
column B if the value in column A exists in the list in column J.

Here is my simple example and formula.

A B ... J
WI 1 AL
NZ 1 AK
IL 1 AS
CA 1 AZ
EN 1 AR


The forumula I am using is as follows
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,J2:J60, 0))), B1:B5).

It appears to be giving correct results (WI, IL and CA are being
summed with england and new zealand left out), however, the MATCH
function as documented is not supposed to take a range as the first
parameter, only a value.

Is this valid, or not, or is there a better way to do this?

Thanks in advance for the help.

Andy

"IN Operator" using Match function....
 

It appears to be giving correct results (WI, IL and CA are being
summed with england and new zealand left out), however, the MATCH
function as documented is not supposed to take a range as the first
parameter, only a value.

I think that technically, it is not using a range, but rather an
array. Arrays are barely documented at all. (as to usage) Many
functions can handle arrays when entered properly, and none of them
claim to do this in the help.