limt array range to increase speed

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

vmccuneAsked:
Who is Participating?
 
barry houdiniCommented:
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
 
vmccuneAuthor Commented:
1000 rows and about 40 columns.
0
 
vmccuneAuthor Commented:
the syntax above returne FALSE in the cell and crtl-shift-enter does not provice the right "}"


0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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

0
 
barry houdiniCommented:
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
 
vmccuneAuthor Commented:
forgot to recalc.  its working.  Checking speed now.
0
 
barry houdiniCommented:
>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
 
vmccuneAuthor Commented:
Great!  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.