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


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.
Neptune ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Rob HensonFinance AnalystCommented:
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.

Rob H

Rob H
Hakan YılmazProject EngineerCommented:
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 "".
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
Rob HensonFinance AnalystCommented:
The IFERROR function came in with excel 2007 so is fine to use unless you need backwards compatibility for users still on 2003.

Neptune ITAuthor Commented:
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.
Neptune ITAuthor Commented:
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.
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Neptune ITAuthor Commented:
As soon as I read yours and  compared it to mine I wondered how I got this far being so naive :)

Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.