Solved

Index Match returning correct result but slow. Need assistance optimizing.

Posted on 2014-12-01
9
74 Views
Last Modified: 2014-12-03
Greetings,

I am trying to create a single page lookup for server data on an excel sheet. When the server name is selected via a drop down and the name selected is placed in A2, what should follow is it's relevant information.

First three columns are simple vlookups and I doubt that they would be slowing it down too much.

I crafted an array to lookup the multiple passwords. It works, but it is very slow and my boss wants it to pop quickly. Here is the array:

=IF(ISERROR(INDEX('Server Passwords'!A:A:'Server Passwords'!B:B,SMALL(IF('Server Passwords'!A:A=$A$2,ROW('Server Passwords'!A:A)),ROW(1:1)),2)),"",INDEX('Server Passwords'!A:B,SMALL(IF('Server Passwords'!A:A=$A$2,ROW('Server Passwords'!A:A)),ROW(1:1)),2))

Open in new window


I am not as proficient in VBA as I would like to be so what I am looking for is a VBA equivalent to this. Just to speed up the response time.

Any assistance is appreciated.
0
Comment
Question by:neptuneit
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40474017
Not clear what r u trying to accomplish with this !
pls post a worksbook and explain in plain English what you want then will seek to it.
gowflow
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40474042
Array formulas are remarkably resource hungry, especially as you have it looking at whole columns eg 'Server Passwords'!A:A

Do you really need to use the whole column, over 1 million rows of cells to be checked?

Can you change it to a fixed range?

If the data is frequently changing in size, you may be able to use Dynamic Range names that adjust with an update of data. Once the data update is complete the Range Name should only need to recalculate once.

Thanks
Rob H

Thanks
Rob H
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40475398
You must use smaller ranges. I suggest you to use Calculated Tables. You don't need to use whole "sheet column", it is enough to tell your formulas to look in "table column".
You can look here for information about creating and deleting tables

And when you use if(iserror(x),"",x) combination, it will first calculate the x, and if answer is false, it calculates x again to show you. You better use iferror function for this. It is enough to say Iferror(x,""). It will only calculate your formula once. If it is error, it will hide x as "".
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40475437
ok here it is change your formula by this one and for sure save it as array CTRL SHIFT ENTER and try it.

=IFERROR(INDEX('Server Passwords'!A1:B100,SMALL(IF('Server Passwords'!A1:A100=$A$2,ROW('Server Passwords'!A1:A100)),ROW(1:1)),2),"")

Open in new window


You may change the 100 to suit your range of users/passwords the less the faster.
gowflow
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 40475609
The IFERROR function came in with excel 2007 so is fine to use unless you need backwards compatibility for users still on 2003.

Thanks
Rob
0
 
LVL 1

Author Comment

by:neptuneit
ID: 40476534
Thanks for the help so far.

The problem is I have multiple arrays on my page which are slowing down the results immensely.

Unfortunately I can't post a workbook as it is sensitive material.

Will try to shorten what it is looking at.
0
 
LVL 1

Author Comment

by:neptuneit
ID: 40476582
Here is something similar though. This is the server password layout:
Server   Password Type          User                  Password
xxxx      Local Admin            administrator                   xxxxxx

There are 109 rows total.

What I am trying to get is each instance of Password type, User and Password that match the server name.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40478211
The formula you have there or the one I posted will lookup in Sheet Server Passwords for the corresponding item that is typed in cell A2 of where this formula lies and will return the value of corresponding Column  B

So in your case is you type in A2 a server name it will lookup and return the Password Type. Only 1 instance.
Is that what you want   ?

this formula has been adapted for 109 rows.

=IFERROR(INDEX('Server Passwords'!A1:B109,SMALL(IF('Server Passwords'!A1:A109=$A$2,ROW('Server Passwords'!A1:A109)),ROW(1:1)),2),"")

Open in new window

gowflow
0
 
LVL 1

Author Closing Comment

by:neptuneit
ID: 40479030
As soon as I read yours and  compared it to mine I wondered how I got this far being so naive :)

Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

26 Experts available now in Live!

Get 1:1 Help Now