Dont you think there should be ways to make excel calculations faster while doing lookups on large data. the answer is yes.... so here are some ways to help you make excel faster while working with lookup function:
Make sure you have understood the options in MATCH, VLOOKUP and HLOOKUP.
MATCH(lookup_value, lookup_array, match_type)
- Match_type=1 returns the largest match less than or equal to lookup value if lookup array is sorted ascending. This is the default
- Match_type=0 requests an exact match
- Match_type=-1 returns the smallest match greater than or equal to lookup value if lookup array is sorted descending
VLOOKUP(lookup_value, table_array, colnum, range_lookup)
- Range_lookup=TRUE returns the largest match less than or equal to lookup value. This is the default option. Table array MUST be sorted ascending.
- Range_lookup=FALSE requests an exact match. Table array does not need to be sorted.
|
Avoid using exact match lookup if possible. |
If you are doing lookup’s using the exact match option the calculation time for the function is proportional to the number of cells scanned before a match is found. For lookups over large ranges this time can be very significant. |
VLOOKUP & MATCH with multiple matches.
If the table you are looking up contains more than one row with a value which matches your lookup value, which row gets found?
- If the table is not sorted the first matching row found is returned.
- If the table is sorted ascending then the last matching row is returned.
VLOOKUP versus INDEX and MATCH or OFFSET.
|
I recommend using INDEX and MATCH. |
VLOOKUP is slightly faster (approx. 5%), simpler and uses less memory than a combination of MATCH and INDEX or OFFSET. |
Converting VLOOKUP to INDEX and MATCH.
These statements return the same answer:
VLOOKUP(A1, Data!$A$2:$F$1000,3,False)
INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)
Speeding up Lookup’s
Because exact match lookups are so slow it’s worth looking for ways of speeding things up:
Use One Worksheet.
|
If speed is critical keep Lookups and Data on the same sheet. |
Keep exact match lookups on the same worksheet as the data they are looking up: It’s significantly faster. |
Use Excel 2000 or later:
|
Upgrade to Excel 2007. |
Excel 2007 is significantly faster than Excel 97,2000 for exact matches. |
Do Lookup in open workbooks
SORT the Data Whenever Possible.
|
SORT your data and use approximate Match. |
Whenever possible SORT the data first, (SORT is very fast) and use approximate match. |
Minimise the Range of Cells you are Looking Up.
|
The smaller the Range the better. |
When doing exact match lookups restrict the range of cells to be scanned to a minimum. |
Sorted Data with Missing Values.
|
Two approximate Lookups are usually faster than one exact Lookup. |
If you can sort your data but still cannot use approximate match because you can’t be sure that the value you are looking up exists in the lookup range, then try this: IF(lookup_val=Index(lookup_array,MATCH(lookup_val,lookup_list),1) ,Index(lookup_array,MATCH(lookup_val,lookup_array), colnum),“notexist”)
This does an approximate lookup on the lookup list, and if the lookup value = the answer in the lookup column you have found an exact match, so redo the approximate lookup on the column you want, otherwise it’s a missing value. Note that this assumes you never lookup a value smaller than the smallest value in the list, so you may need to add a dummy very small entry into the list. |
CAclubindia
