How to make the array work?

[feipezi]

Hi,
I have 52 states and try to break into 5 regions. An Excel macro has been set up using Select Case approach and successful. But if I have 40k rows, it's going to be slow. So I've tried to use worksheet functions to set up formulas to make the process faster but not working:



=IF(A2={"AL","TN","KY","MO","AR","MS","LA","KS","OK","TX"},"4Z",IF(A2={"WA","OR"...



I tried OR function and it worked:



=OR(A2={"AL","TN","KY","MO","AR","MS","LA","KS","OK","TX"})



But it won't assign me the region code and only give me True and False.
Could you help me out there?
Thanks in advance.

 

[bluedragon2]

Setup a table of states and their value then use the VLOOKUP function.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[feipezi]

Thanks Blue! But my question is how to make the Array work. I don't want a table for Vlookup(); I don't want VBA macro either like 'Select Case...'. In fact, I am seeking another approach.
Are you implying the Array is not going to work? But why?

 

[SkipVought]

only give me True and False.

That is because you do not have false result in one of your embedded IF() functions.

Check your Pairs.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, Blue's approch is a much, MUCH better alternative than literals in a multi-embedded IF() structure, as you insist on pursuing. NOT a best and accepted practice!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gaudett]

Actually your approach will slow things down. By inserting a formula like this you will be forcing the spreadsheet to evaluate this each time it updates....... With 40k like this it can take quite some time

Terry