Solved

limt array range to increase speed

Posted on 2011-09-12
8
246 Views
Last Modified: 2012-05-12
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
Comment
Question by:vmccune
  • 5
  • 3
8 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
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

by:vmccune
Comment Utility
1000 rows and about 40 columns.
0
 

Author Comment

by:vmccune
Comment Utility
the syntax above returne FALSE in the cell and crtl-shift-enter does not provice the right "}"


0
 

Author Comment

by:vmccune
Comment Utility
I got the syntax copied and it works but if I copy it down I get the same value in each row.

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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

by:vmccune
Comment Utility
forgot to recalc.  its working.  Checking speed now.
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
>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

by:vmccune
Comment Utility
Great!  Thanks.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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;…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now