In the dynamic world of data analysis, mastering Excel and its powerful functions is crucial for efficient information retrieval. Among these functions, the trio of lookup formulas; MATCH, VLOOKUP, and HLOOKUP stands out for their indispensable role in looking up data in Excel workbooks. However, as datasets grow larger, the performance of these functions may encounter challenges, requiring users to explore optimization techniques.
The performance of VLOOKUP, HLOOKUP, and MATCH functions has been improving significantly since Office 365 version 1809 (October 16th, 2018). In this guide, we delve into strategies to enhance the calculation speed of your lookup formulas, particularly beneficial for users operating on older versions of Excel.
Functions and lookup types
MATCH, VLOOKUP, and HLOOKUP functions have optional arguments that determine the match and lookup types. Let's go over these formulas in more detail.
MATCH
The MATCH function can contain an optional match_type argument which specifies how lookup_value is to be matched with values from the lookup_array. The match_type can take three values:
- match_type = 1 is the default value and returns the highest match that is less than or equal to the lookup value. This is an approximate match and assumes that the data is sorted.
- match_type = 0 is exact match and assumes the data is not sorted.
- match_type = -1 returns the smallest match that is greater than or equal to the lookup value. This is an approximate match and assumes the data is sorted.
VLOOKUP
The range_lookup is an optional argument that specifies the match method that allows selecting whether the formula should look for an exact match. It can take Boolean values like TRUE or FALSE:
- range_lookup = TRUE is the default value and returns the largest match that is less than or equal to the lookup value. This is an approximate match and assumes the data is sorted.
- range_lookup = FALSE is exact match and assumes the data is not sorted.
Speed up lookups
Sort Data
If possible, sort your data! Unsorted data has a significant effect on lookup performance. Using approximate match in addition to sorted data will give you substantial performance gains. You can find different ways of sorting your data here: How to sort in Excel.
Use one worksheet
If you can keep the lookups and data on the same sheet, you will get better performance. This tip, combined with lookup range restriction below will also help you organize your data.
Restrict lookup range
If you need to use exact match lookup, avoid including any cells in the search range. For example, if your data is spread across 5 rows, do not select 10 rows in the lookup function. If your data is dynamic in layout, use tables and structured references. or dynamic range names rather than referring to a large number of rows or columns.
An Excel Table can adjust its size automatically. Use a structured reference when referring a range from a Table and the reference will update as well. Here is a structured reference use case in a lookup function:
The MATCH looks lookup_value in a column of a defined Table.
Another alternative to restricting the lookup range is creating named ranges that refer to dynamic ranges. Dynamic ranges can be created using functions like OFFSET, INDEX and COUNTA. We suggest using the INDEX function, because the OFFSET is a volatile function that may cause performance issues in some cases.
OFFSET:
INDEX:
Moving the COUNTA to another cell or a named range and adding a reference to it in the actual formulas will result in better performance. For example, create a named range called CountMe with a formula:
Now, you can use this named range in dynamic range formulas.
OFFSET:
INDEX:
Use the approximate match option instead of exact match
Approximate matches are almost always calculated faster than exact matches. However, the data needs to be sorted for this approach. Approximate match options return the nearest value when no results are found. To prevent this, you can use two approximate matches in a single formula:
VLOOKUP(lookup_value, table_array, col_index_num, True), "not found")
Check to see whether the lookup value is actually in the lookup list. If the lookup value is found, the expression returns TRUE and execute the actual lookup. Otherwise, the formula will return the FALSE string that we set for "not found".
In conclusion, as we navigate the expansive landscape of Excel and its diverse functions, the optimization of lookup formulas becomes paramount for seamless data analysis. Whether it's harnessing the power of MATCH for precise value identification or utilizing the flexibility of VLOOKUP and HLOOKUP, understanding the nuances and employing performance-enhancing strategies is key. Sorting data, consolidating worksheets, and intelligently restricting lookup ranges are fundamental practices that not only boost calculation speed but also contribute to a more organized and efficient Excel experience. So, armed with these insights, venture forth into your data-driven endeavors with the confidence that your Excel prowess is now fortified for optimal performance.
