IS there a size limit to using an array?

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

rudekid

Am trying to use MATCH function to locate missing or incorrectly spel
country names in a spreadsheet of address data.

Here's the formula:

=IF(ISERROR(MATCH("*"&countries&"*",ImportData!$D1:$Z1,0)),0,1)

Doesn't work and BOY is that annoying cos I don't understand why unles
it has something to do with the size of the array or the amount o
information I'm trying to cross reference again.

I've tried:

re-typing and re-formatting the array, (failure).
tested smaller bits of the array, (failure)

The thing is, I have another array which is larger in row size - 45
lines as opposed to this country array of 250 - and that works fin
having IDENTICAL syntax. The only reason I can think it does work i
that in each field there is only one character.

Incidentally, I can't change the ImportData array as I don't know wher
the country field will be each time.

thanks for assistanc
 
Hi
you are using a name 'countries' in your formula. What cell is
referenced by that name?
The first parameter of the MATCH function can only be a single value
not a range
 
that can't be true though: I've been using code that does exactly tha
for ages. E.G.

=SUM(IF(ISERROR(MATCH("*"&Street4Check&"*",RawData!I166:K166,0)),0,1))+SUM(IF(ISERROR(MATCH("*"&Street4Check&"*",RawData!M166,0)),0,1))

okay, a bit convoluted but Street4Check looks like this:

P O
P. O.
PO.
PO
BP
B.P.
B. P.
B P
Box
Boite

it checks for anything in fields I:K which matches the above and if i
can't find it returns 1. I've checked the code again and it work
perfectly. I have quite a few others like this so there must b
something more to it - help
 
Hi
o.k. but you have array entered this formula and it will return '1' if
one of your Steet4Check is found in your RawData range. So a '0' would
indicate that none of your strings is found in the range.

In your formula you may change it to
=SUM(IF(ISERROR(MATCH("*"&countries&"*",ImportData!$D1:$Z1,0)),0,1))

this returns 1 if any entry in countries is fount in your range
 
some people may have spotted an earlier reply I swiftly deleted once I'd
taken a closer look at the code.

However, I don't think I'm going mad. Take a look at this code:

=SUM(IF(ISERROR(MATCH("*"&Room&"*",RawData!G26:J26,0)),0,1))+SUM(IF(ISERROR(MATCH("*"&Room&"*",RawData!L26:M26,0)),0,1))

The array "Room" looks like this:

Rm
Room

If it finds either entry in any of the fields G-J, or L-M it flags the
field with 1.

Therefore, in principle my earlier code should be fine, (and yes: I
have just re-checked the above to make sure it works).
 
ah, yes: so adding the SUM function gets it to work. This explains th
confusion: somebody else had created this function before me and
adapted it without really coming to grips with it.

Don't suppose you can explain why without SUM it doesn't work properl
could you?

thank
 
Hi
quite easy :-)
you have created an array formula which returns results for each cell
in the array it processes. The SUM function adds all theses results.
Without SUM you will see only the first result of your formula
 
sorry: that doesn't work: the first 6 countries aren't in the array an
- sure enough - it returns a zero. After that it returns 1 (because i
finds Argentina, which is in the array) but when it comes to the nex
entry that isn't in the array it returns 1 again. :(

Also, I'm confused: you said in your first response that the "firs
parameter of the MATCH function can only be a single value
not a range"

but isn't that contradicted by the example that I give
 
sorry if this is getting a bit confusing by the way: I keep posting
message, realising it's nonsense, deleting it but then somehow it pop
up again.

Still confused about this problem though.

Incidentally, some of my fields are returning 2. If I understand you
explanation correctly then this should be because it finds the countr
or at least, an element in the array in more than one field: have
understood this correctly?

thank
 
Hi
my first answer was based on the assumption that you enter the formula
in a 'normal' way. It works if you enter it as array formula :-) -> so
my misinterpretation!

For your other problem can you gibe a little bit more information:
- example data of your range with the country names
- example data for the range you test against theses countries
- the exact formula you use
- your espected result
 
I've figured it out!

It's the wildcard that was creating the problem. I've no idea why but
it doesn't really matter since I've realised that I should just be
searching for the country to appear on its own in one field rather than
embedded in another lot of information anyway, (for example it would
return 1 if it found Lausanne since it finds "*USA*" in the data.

thanks for all your help though and if you can explain why the
wildcards would ruin the result that would be even better.
 
Back
Top