|Importance of Speed||Faster Calculations||Microsoft's Advice||Bottlenecks/size|
|Lookups||Dynamic Ranges||Totalling||Generating Workbooks|
|Multi-Level Calculations||Array Formulae||SUMIF Example||Worksheet Links|
|Lookup Options||Speeding Up Lookups||Missing Values|
|Multiple Cells||Two-Dimensional Lookup||Three-Dimensional Lookup|
Lookups are often the most significant factor in calculation times, and fortunately there are many ways of improving lookup calculation time.
With FastExcel Version 4 you can now use the AVLOOKUP function, which is faster than VLOOKUP and INDEX/MATCH in many circumstances.
Make sure you have understood the options in MATCH, VLOOKUP and HLOOKUP.
MATCH(lookup_value, lookup_array, match_type)
VLOOKUP(lookup_value, table_array, colnum, range_lookup)
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?
Converting VLOOKUP to INDEX and MATCH.
These statements return the same answer:
Because exact match lookups are so slow its worth looking for ways of speeding things up:
Use FastExcel's AVLOOKUP function.
AVLOOKUP is significantly faster than VLOOKUP in many circumstances.
Use One Worksheet.
Use Excel 2000 or later:
SORT the Data Whenever Possible.
Minimise the Range of Cells you are Looking Up.
Unsorted Data with Missing Values.
Looking Up a Set of Contiguous Rows or Columns.
Looking Up a Rectangular Block of Cells.
You can use MATCH and OFFSET to return a rectangular block of cells as a range.
In large spreadsheets you often need to lookup using multiple indexes, such as looking up product volumes in a country.
The simple way to do this is to concatenate the indexes and lookup using concatenated lookup values. This is inefficient when the data is sorted for two reasons:
It is often more efficient to calculate a subset range for the lookup: for example by using COUNTIF to count the number of rows for each country and then calculating the first and last row for each country from the counts, and then looking up the product within that range. See SUMIF Example or the FastExcel sample problem for an example of using this technique.
The FastExcel Version 4 AVLOOKUP function has built-in methods to easily and efficiently handle multiple-index lookup.
If you need to lookup the table to use as well as the row and the column here are some techniques you can use, focussing on how to make Excel lookup/choose the table.
If each table you want to lookup (the third dimension) is stored
as a set of range names, or as a table of text strings that represent
ranges, then you may be able to use INDIRECT or CHOOSE.
The example above dynamically uses TableLookup_Value to choose which range name (TableName1, TableName2, ...) to use for the lookup table.
INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
This example uses INDIRECT and TableLookup_Value to dynamically create
the sheet name to use for the lookup table. This method has the advantage
of being simple and can handle a large number of tables, but because
INDIRECT is a volatile function the lookup will be calculated at every
calculation even if none of the data has changed.
Another technique is to aggregate all your tables into one giant table, but with an additional column which identifies the individual tables. You can then use the techniques for multiple-index lookup above.
AVLOOKUP, AMATCH, MATCH,VLOOKUP and HLOOKUP allow you to use the wildcard characters ? (Any single character) and * (no character or any number of characters) on alphabetic exact matches. Sometimes this can avoid multiple matches.