Lookup functions are extremely useful when working with large data sets. Although they almost always work instantaneously with small data, larger data sets is when things can get rough in terms of performance, as large operations can require more computational power.

The performance of VLOOKUP, HLOOKUP, and MATCH functions has been improving significantly since Office 365 version 1809 (October 16^th, 2018). We are going to cover some methods that can help improve calculation performance of your lookup formulas, especially if you are using an older version of Excel.

Download Workbook

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:

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".