# array alternatives

1. ## array alternatives

now i'm not a fan of arrays and always try to find an alternative here are 3 alternatives to 3 things normally seen done with array entered formulas
any more that can be added? or any improvements(apart from an array alternative of course) or is there anything plain wrong?

2. ## Re: array alternatives

Aside from adding a function (INDEX) to avoid the necessity of CSE, I'm not sure I see the point. I wouldn't expect they calculate any faster. What am I missing?

3. ## Re: array alternatives

@martindwilson

Or same example but with pivot table (added in the file).

4. ## Re: array alternatives

well i think they are easier! i can remember them and reconstruct them at a drop of a hat,plus you often see wonderful constructs of things like ROW(A27:A50)-ROWs(A1:a26)which i never see the point off

Ah. OK .

6. ## Re: array alternatives

but ive just timed them all and the arrays tend to be marginally faster than the equivalent index construct so maybe the gain is worth it however its a very small difference .for example the
lookup on two values over 7000 rows is array average of 0.001826 s and the index combo is 0.002089s

7. ## Re: array alternatives

Hello Martin,

For a 2 criteria lookup you can use LOOKUP function like this

=LOOKUP(2,1/(\$B2:\$B25=\$K1)/(\$A2:\$A25=L1),\$C2:\$C25)

LOOKUP, like SUMPRODUCT typically doesn't require CSE. That will get the same results as your version given your example values - although where there are multiple matches it returns the last one rather than the first...

The MAX(IF version could conceivably return an incorrect result if the max value is < 0 (it'll return zero) and of course MIN(IF isn't possible for similar reasons.....

8. ## Re: array alternatives

But you could have used in F9:

Formula:
`Please Login or Register  to view this content.`

9. ## Re: array alternatives

I like this idea in the multi lookup of LARGE with 1/ROW() as opposed to SMALL with ROW() to get round the zero issue. One to remember.

Still probably going to use an array version, though!

Cheers

10. ## Re: array alternatives

@snb reason i dont use rows(\$1:1) or row(\$1:\$25) is f4 doesn't work on those and i'm no typist.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1