Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

How to use huge number of VLOOKUP formulas in excel sheet (more than 50,000 formula) without harming sheet fnctionality (calculation time)?

user-image
Question added by emad awad , internal audit manager & cost control manager , nile sugar
Date Posted: 2013/07/16
Mike Emerson Pasaron
by Mike Emerson Pasaron , Safety Officer , Arabian Petrochemical Co. (PETROKEMYA)

If you want to return values or make lookups for huge data, VLOOKUP is not the right formula.
To do it without using macros, you would need to use INDEX+MATCH formula and named ranges.
You just need to update the named range if you want to change the data source.
VLOOKUP is a volatile formula and when used in huge data, most likely it will hang.
If you want to do it using macros, Johan has given you the solution.

Deleted user
by Deleted user

Use3 worksheets..
On worksheet1 the source listing that has to be completed, and on sheet2 only one single vlookup formula.
Sheet3 will be the results sheet.
Then create a macro that copies one by one the records from the source sheet to the source fields of the vlookup formula on sheet2.
The macro then can copy the results (values) of the vlookup formula to the results sheet (3).
This method I often used on systems with very limited resources and citrix environments.
The file size will stay the smallest possible, and running the macro often takes less time as (re)calculating an enormous amount of formulas.
Another advantage, the source data can be changed, without any need to change macro or formulas.

Muhammad Jahanzaib Khan
by Muhammad Jahanzaib Khan , Accountant , Mairaj Uddin Traders

first of all make database worksheet and select your data and make data table after give name your data table

than work with vlookup on next worksheet

when you put your data table name using vlookup it's work

More Questions Like This