Vlookup

[Monty]

Hello Everyone.

We all know how faster v lookup and what benefit we have with that...But unfortunately it becomes slow while working with one lakhs rows takes almost two hours to finish the job.

It's a simple lookup but why taking so much time..

Any other option to do the same..May be access loading two data sheets into access as two table and doing the same task in a seconds...May be.

System:
With 4GB ram and 64bit taking 2 hours of time to refresh the vlookup.

Please suggest! Vlookup in Excel or Dlookup in Access.

Thanks
Monty!

 

[AlanSidman]

Join your two tables in Access and then run a parameter query for the item you are looking for. Here is an example of where two tables are joined and you require the data from table 2 Field2 for the item "abc" in table 1 Field1.

SELECT Table1.ID, Table1.Field1, table2.Field2
FROM Table1 INNER JOIN table2 ON Table1.Field1 = table2.Field1
WHERE (((Table1.Field1)="abc"));

 

[vletm]

Monty
... a sample file
... where is a sample file?

 

[Monty]

Hello AlanSidman...thank you so very much for the query example let me give a try not so familiar with Access though.

Hello vletm...It's a generic question to understand whether it is possible to speed up in excel or need to use access...thanks!

 

[vletm]

Monty
A generic answer - many times there would be possible to find faster way ...
but without a sample file, it would be challenge to find solution for You case or
are You ready to change everything ... no matter what do You have there?

 

[Deepak]

Just checked that sorting 1M rows * 8 columns doesn't take time. So do the same first then dual vlookup combination will be much faster.

 

[Monty]

Hello Deepak...Long time..

dual vlookup combination will be much faster.?

Need to understand this...

 

[Deepak]

Pls have a read here...

https://exceljet.net/formula/faster-vlookup-with-2-vlookups

Importing to access & then do the query will take more time as i expected & checked.

 

[Monty]

Thanks freak for checking...I was following that site but hardly got any help...Though article was good...Problem is vlookup is not same for the same column it's dynamic...So that is why seeking advice....Which is best...

 

[AlanSidman]

Here is the sample db that shows the SQL statement I provided above. Also, instead of uploading the tables, you could link the tables that are in Excel and they will operate the same as if the tables were actually uploaded.