Solved

# limt array range to increase speed

Posted on 2011-09-12
246 Views
I am using the following as an array lookup and it works but the speed is killing me.

={IFERROR(INDEX(Data!F:F,MATCH(\$A\$31&\$C39,Data!A:A&Data!D:D,0)),0)}

I only need columns A-J and Rows 5 - 2000 with row 5 being the field name.  How can I adjust this lookup to get me more speed?

Thanks

0
Question by:vmccune
• 5
• 3

LVL 50

Accepted Solution

barry houdini earned 500 total points
Clearly you can limit the format to those rows,....perhaps also a different syntax, try

=IFERROR(INDEX(Data!F\$5:F\$2000,MATCH(1,IF(Data!A\$5:A\$2000=\$A\$31,IF(Data!D\$5:D\$2000=\$C39,1)),0)),0)

confirmed with CTRL+SHIFT+ENTER

How many of these do you have?

regards, barry
0

Author Comment

1000 rows and about 40 columns.
0

Author Comment

the syntax above returne FALSE in the cell and crtl-shift-enter does not provice the right "}"

0

Author Comment

I got the syntax copied and it works but if I copy it down I get the same value in each row.

0

LVL 50

Expert Comment

It's working OK for me......!

Sorry if you know.....how are you doing CTRL+SHIFT+ENTER? You need to click in the cell with the formula and then press F2 to select - then hold down CTRL and SHIFT while pressing enter.....

Is the A31 fixed for all the formulas with C39 changing as you copy down - what about the lookup ranges does col A + Col D become B and E then C and F etc.?

barry
0

Author Comment

forgot to recalc.  its working.  Checking speed now.
0

LVL 50

Expert Comment

>I got the syntax copied and it works but if I copy it down I get the same value in each row

make sure calculation is set to auto....or press F9 to re-calculate.....
0

Author Closing Comment

Great!  Thanks.
0

## Featured Post

### Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…