multiple arrays in single statement

  • Thread starter Thread starter rudekid
  • Start date Start date
R

rudekid

does anyone know if you can refer to more than one array in a single
Excel statement?

I can't find any references in Excel help or textbooks saying I can't
but at the same time, can't get a statement to work unless I convert
one of the arrays to a text lookup.

It's a problem because I need to look up more than one reference. If
anyone knows the answer or a way round it this would help me going the
wrong way with fixing the problem.

For the record the statement I am trying to get to work is as follows:

=SUM(IF(NOT(ISERROR(MATCH("JP",RawData!U2,0))),0,IF(ISERROR(MATCH("*"&IllegalChars&"*",RawData!E2:P2,0)),0,1)))

Where "JP" was an array.

At the moment, if U2 contains JP using the above statement it returns 0
but if I use an array with the same value it returns #N/A
 
I think you've overcomplicated the example, which makes it hard to
reproduce; can't you articulate your problem using simply, e.g.,
=MATCH(JP,iRange,0) and =MATCH(JP,iArray,0)?

And I'm not sure what you mean by '"JP" is an array'. And I can't sort
out the meaning of "if U2 contains JP using the above statement it
returns 0 but if I use an array with the same value it returns #N/A".
What do you mean "If U2 contains JP?" If you use an array instead of what?

In any event, if JP is a defined name referring to {"x","z"} and iRange
refers to A1:C1 and contains x,y,z, and iArray is a defined name
referring to {"x","y","z"}

both =MATCH(JP,iRange,0) and =MATCH(JP,iArray,0) return 1 and 3 if array
entered into a two cell row.

What are you trying to do? Keep it simple.

Alan Beban
 
does anyone know if you can refer to more than one array in a single
Excel statement?
Click to expand...

Yes, but the arrays need to conform to each other. For example,

{1,2}*{3,4} = {3,8}
{1;2}*{3;4} = {3;8}
{1,2}*{3;4} = {3,6;4,8}
{1;2}*{3,4} = {3,4;6,8}

but

{1,2,0}*{3,4} = {3,8,#N/A}

while

{1,2,0}*{3;4} = {3,6,0;4,8,0}

I can't find any references in Excel help or textbooks saying I can't
but at the same time, can't get a statement to work unless I convert
one of the arrays to a text lookup. ...

Textbooks?

For the record the statement I am trying to get to work is as follows:

=SUM(IF(NOT(ISERROR(MATCH("JP",RawData!U2,0))),0,
IF(ISERROR(MATCH("*"&IllegalChars&"*",RawData!E2:P2,0)),0,1)))

Where "JP" was an array.

At the moment, if U2 contains JP using the above statement it returns 0
but if I use an array with the same value it returns #N/A
Click to expand...

Do you mean that the formula above works, but

=SUM(IF(NOT(ISERROR(MATCH({"JP"},RawData!U2,0))),0,
IF(ISERROR(MATCH("*"&IllegalChars&"*",RawData!E2:P2,0)),0,1)))

doesn't? Or do you mean something like

=SUM(IF(NOT(ISERROR(MATCH({"JP","XY"},RawData!U2,0))),0,
IF(ISERROR(MATCH("*"&IllegalChars&"*",RawData!E2:P2,0)),0,1)))

doesn't work? If you mean the former, then please show the *EXACT* formula that
*DOESN'T* work, not a roughly similar formula that does work. If you mean the
latter, then the answer is that your array in the 1st MATCH call is likely
doen't conform to the range in the second match, RawData!E2:P2.

Even so, I have to wonder if you're making a mistake putting the entire two-part
IF expression inside SUM. The way you've written it, if the 1st argument to the
1st MATCH call is a scalar (i.e., neither an array nor a range) this gives a
single (scalar) result, which if true returns 0 to SUM, and if false returns the
result of the second, inner IF call. If that's what you want, you'd be better
off using

=IF(COUNTIF(RawData!U2,"JP"),0,
SUMPRODUCT(--(COUNTIF(RawData!E2:P2,"*"&IllegalChars&"*")>0)))

On the other hand, if the 1st argument to the 1st MATCH call is an array, either
it needs to conform to the 2nd argument to the 2nd MATCH call, or you need to
explain in much greater detail what you're trying to accomplish in your first IF
call. I can think of several alternatives.


1. 1st arg to 1st MATCH call and 2nd arg to 2nd MATCH call should conform.

=SUMPRODUCT(COUNTIF(RawData!U2,JP_array),
--(COUNTIF(RawData!E2:P2,"*"&IllegalChars&"*")>0))


2. 1st IF call should be construed as if any of the entries in JP_array match
RawData!U2, then return 0 (period) else sum of the results from the 2nd IF call.

IF(SUMPRODUCT(COUNTIF(RawData!U2,JP_array)),0,
SUMPRODUCT(--(COUNTIF(RawData!E2:P2,"*"&IllegalChars&"*")>0)))


3. 1st IF call should be construed as if all of the entries in JP_array match
RawData!U2, then return 0 (period) else sum of the results from the 2nd IF call.

IF(SUMPRODUCT(1-COUNTIF(RawData!U2,JP_array))=0,0,
SUMPRODUCT(--(COUNTIF(RawData!E2:P2,"*"&IllegalChars&"*")>0)))
 
thanks for the help, will check these out

in case bamboozled by function this is what I was trying to do:

This is one of many functions designed to test for errors in addres
data. Previously the function merely looked at an array of cell
containing dodgy characters then compared it with an array of addres
fields and returned an error of 1 no matter how many illegal character
it found or 0 if it found none.

What I was trying to do is build an exception if the countries fo
those entries were either in Singapore or Japan. So, in my formula:

"JP" was an array of 2 codes, JP and SG representing the countrie
Japan and Singapore

"Illegalchars" is the array of illegal characters.

So:

=SUM(IF(NOT(ISERROR(MATCH("JP",RawData!U2,0))),0,IF(ISERROR(MATCH("*"&IllegalChars&"*",RawData!E2:P2,0)),0,1)))

(was supposed to) check if the address was Singapore or Japan. If i
was, return a 0. If it wasn't, then check the rest of the address fo
the usual criteria, i.e. were there any illegal characters?

Entering the above just returned #N/A regardless of the data entered i
U2
 
Back
Top