Use Index+Match function in Excel macro instead of Vlookup

[monhw]

Hi there,

I don’t know much about Excel Macro, has been learning by trial & error as well as googling…..unfortunately, now I’m stuck with a problem and hopefully can get some help from the experts here.

I have 2 spreadsheets, one is like a form for users to fill in a customer number, then click the “Get data” button to initial a macro, which will connect to another Excel spreadsheet located in the company intranet, to search the records database which is over 10,000 rows, once found, to return the customer information like purchase date, what produce, what model….etc. back to the Result.xls

I have been using VLOOKUP, but when the database grows bigger, it seems that VLookup is slowing down especially when try to connect to the remote file to search for the data. I was told that Index & Match will be quicker so would like to give it a go….but it seems I can’t get the Index & Match to work in the macro.

I have attached the 2 files as well as the macro I wrote which keeping coming back with “#Value”, I have been searching for a while on the google without much luck……appreciate if anyone out there could shed me some light on what I have done wrong.

Thanks.

[Richard Buttrey]

Hi,

Why bother with a macro? Why not have the cells permanently linked to the data workbook.
e.g. in B5 of the Results sheet

Please Login or Register  to view this content.

[monhw]

Thanks Richard. We don't want to have it permantly linked to the data workbook which is huge and is located in a remote server in the US. The Result.xlsm spreadsheet is being used worldwide, and not every user will need to have the customer information all the time, they hit the "Get data" button to connect to the remote file and have the required customer information back when they need it. We want to minimize the size of the Result.xlsm for easy distribution.

[Leith Ross]

Hello monhw,

This version of your macro should do the job. Since the macro is being run from an Excel workbook, there is no reason to create a separate instance of Excel. You can open the other workbook in the current instance.

Please Login or Register  to view this content.

[monhw]

Hi Leith,
Thanks for the script, it works almost as what I want....however, when I tried to apply it to my real data, I bumped into the following problems:

1. My actual data goes from Column A to Column W, when applying your macro, the data return stops at Column G, the rest are all #N/A, is it because some of the cells have no value in it??
2. After the data was successfully pull to the Result.xlsm workbook, I noticed that the Data.xlsm is still open and you have to manually close it. Is it a way to get the data without opening the Data.xlsm at all as we don't want the users to see the other customers information......
3. As we already know the customerID, so we don't need to display it again in the Result.xlsm, the required information from the Data.xlsm should starts from Column B to Column W. I tried to change it without any success.....:-(

Thanks again for your help, it saves me heaps of time by trying different scripts.....

[Leith Ross]

Hello monhw,

Here is the updated macro code.

Please Login or Register  to view this content.

[monhw]

Hi Leith,

Thanks so much, it works like magic, exactly as what I want.

Unforunately when I put it in real life, I have the following problems:

1. When it opening the remote data.xlsm file that locate in one of our company server overseas, it has the Microsoft pop-up box saying that it is opening the file with the path showing....
2. Then I have an Run-time error '-2147352565(8002000b)' Index refers beyond end of list. The scripts stop at
Set SrcRng = SrcWkbWorksheets("Data").Range("A1").CurrenRegent
is it because the data file is too big??

My other question out of curiosity is that will this FIND Function faster than the Index&Match Function??

Thanks again for your time and effort.

Regards,
Monhw

[monhw]

Hi Leith,

I had another look and now I know why I'm having the run-time error as I have got the worksheet name wrong.

Now that I fixed the issue, the macro runs beautifully, however, it seems that it is a bit slow, everytime the "GetData" button is hit, it has to download the Data.xlsm file from the server which takes more than 30 sec. When comparing to the VLOOKUP which is slow the very 1st time but once the connection is established, it is very quick, only 10 sec or so for the subsequent search.

In changing the searching method, my aim is actually trying to improve the time required in returning the data, so if you know of any method that can give a faster lookup will be much appreciated.

Thanks again,
Monhw

[Leith Ross]

Hello monhw,

The only other option I can think of to speed things up might be to pull the data from the workbook when it is closed. This can be done using ADO and should be faster, but you never really know till you try it. There are other factors beyond you control that will affect the overall speed: System loading, routers, number of users, etc.

[monhw]

Thanks Leith, thanks for the tips. I have been reading a few articles mentioned that Index+Match cannot be used in a closed workbook but VLOOKUP can, is it the case? Just trying to understand if Index+Match an option or not before I go further down the track.....

Regards,
Monhw

[Leith Ross]

Hello monhw,

Honestly, I don't know. I seldom work with closed workbooks. I do know the Index is a volatile function and the ADO reads static data stored in the workbook. So, it would follow that any volatile function probably can not be used in retrieving data from a closed workbook.

[monhw]

Hi Leith,

Thanks for that, at lease now I know Index might not be a good solution.

I've been able to use the below ADO script I found on the web, it works perfectly when the Data.xlsm is local, once I point to the actual Data.xlsm in the web, I have the 'run time error '-2147467258(80004005)': Invalid internet address. The script stopped at oRS.Open sSQL, sConnect, adOPenForwardOnly, _adLockReadOnly, adCmdText. Any idea??

Please Login or Register  to view this content.

Thanks a lot for the information.

Regards,
Monhw