which is faster lookup
- Thread starter Meghnath
- Start date
Chihiro
Excel Ninja
Depends. Is it on sorted list and single criteria lookup?
Then use double approximate lookup.
https://exceljet.net/formula/faster-vlookup-with-2-vlookups
If list is unsorted and/or multiple criteria lookup. It will depend on your workbook set up and version of your Excel etc.
Here are some options.
1. Load data to PowerQuery and do your lookup operation there.
2. Use VBA, using Scripting.Dictionary and array objects.
3. Use VBA, create temp copy of workbook and query from temp workbook using ADO to do lookup (via SQL Query).
Upload sample file (with about 50 data sample) and your expected output.
Then use double approximate lookup.
https://exceljet.net/formula/faster-vlookup-with-2-vlookups
If list is unsorted and/or multiple criteria lookup. It will depend on your workbook set up and version of your Excel etc.
Here are some options.
1. Load data to PowerQuery and do your lookup operation there.
2. Use VBA, using Scripting.Dictionary and array objects.
3. Use VBA, create temp copy of workbook and query from temp workbook using ADO to do lookup (via SQL Query).
Upload sample file (with about 50 data sample) and your expected output.
Somendra Misra
Excel Ninja
Hi,
If you are adding hundreds of vlookup formulas in that data, than it will hang your systems.
Try converting your raw data to excel table and that use that to make a pivot table that way your pivot will keep updating just at a click of refresh button and no need to change the data source. Just keep adding the data below table.
Or you may try other suggestions given here.
Regards,
If you are adding hundreds of vlookup formulas in that data, than it will hang your systems.
Try converting your raw data to excel table and that use that to make a pivot table that way your pivot will keep updating just at a click of refresh button and no need to change the data source. Just keep adding the data below table.
Or you may try other suggestions given here.
Regards,
Nightlytic
Member
I personally like to do just a match by itself, to find the row position of the data I am trying to retrieve, copy the match results and paste as values on top of one another (improves performance as they don't recalculate then), then in next column do =Indirect( "'Sheet1'!D"&C2) assumes you want value from sheet1, column D, and your matched value is in C2. When it's calculated, again copy and paste as values.
Not the most sophisticated, but this works great if you are trying to quickly add a few fields from one dataset to another without much fuss. The match only happens once and then all other formulae will just refer to that result.
Not the most sophisticated, but this works great if you are trying to quickly add a few fields from one dataset to another without much fuss. The match only happens once and then all other formulae will just refer to that result.