Excel 2007:VBA Code: Any alternate to vlookup function

[vidyuthrajesh]

Hello,

is there any alternate to vlookup function to search for larger number of rows/col ( half-million plus..) as it is time consuming

1. if i use the normal vlookup function the system freezes ( just a formula and no vba code)
2. i use vlookup function in vba code in two ways but it takes more then 45 minutes to complete ( approximate 20 seconds per 1000 records)
a. through code i write the formula in the cell and copy it to every 500 rows and then convert as values (just to see something happening!)
b. through code i store the result of the formula in the variable and write back the variable in the cell ( cell by cell)

as mentioned both is heavily time consuming...is there any alternate ( probably database functions?) which takes the entire data set( like sql) and not each and every individual cell..

Thx

[dilipandey]

Hi Vidhuthrajesh,

welcome to the forum.

Try looking at DGET database function. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[vidyuthrajesh]

Hi Dilip,

Appreciate your response. DGET also navigates cell by cell....is my understanding right?? and if so, this may not bring down the processing time... thx

[dilipandey]

Hi Vidyuthrajesh,

I have suggested DGET as you have asked for alternate Database function. Regarding their calculation speed, they should be faster as they are "DATABASE" function. Also you can search "Excel Database function: how efficient are they" over google to learn more. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[royUK]

Using VBA will be no faster than having the formulas in the worksheet. With the amount of data it will be slow.

If your code uses a loop then again it will be slow.

Instead of just stating the problem try explaining what the purpose of the formula/code is & post the code

[fzz]

I assume you're using VLOOKUP(x,y,z,FALSE) to find exact matches. Better to sort the table (y) in ascending order on the first column so you could use VLOOKUP(x,y,z). This latter form uses binary search, so even with 1,048,576 (2^20) rows, it'd take at most 20 comparisons to find the closest match, and just one more comparison to ensure it's an exact match.

VBA isn't going to make this better unless you use it to create static sorted lists or binary trees, and that can eat a lot of memory. Better to sort very long lists in place.