SpeedTools AVLOOKUP2 & MEMLOOKUP versus VLOOKUP – Performance, Power and Ease-of-Use Shootout Part 1

Its time for some performance tests to see how the new functions in SpeedTools stack up against the standard Excel functions. First up is MEMLOOKUP and AVLOOKUP2 vs VLOOKUP!

SpeedTools Lookups are easier to use, more powerful and less error prone than VLOOKUP or INDEX/MATCH

Having the right Default Parameters helps Ease-of-Use

Most people want their LOOKUPs to tell them when the thing they are looking for does not exist in the lookup table. And most of the time people are working with unsorted data.
Unfortunately VLOOKUP’s default settings don’t do that: it defaults to trying to give you an approximate match on sorted data.

So if you use the VLOOKUP defaults you will probably get the wrong answer!
MEMLOOKUP always does an exact match, even with sorted data (but it will still use fast binary search if you have sorted data and tell MEMLOOKUP about it).
AVLOOKUP2 also defaults to unsorted data and exact match, with an option for approximate match on sorted data if you are sure thats what you want.

Here is an example of VLOOKUP getting the wrong answers when using its defaults:

And here is the same example showing MEMLOOKUP getting the correct results.

Simplify Lookups with built-in error handling, header labels and more!

Use both Exact Match and Approximate Match with Sorted Data

AVLOOKUP2 has separate parameters for sorted data and exact match, and can use the superfast binary search algorithm on sorted data for both exact match and approximate match.

Header Labels

Both MEMLOOKUP and AVLOOKUP2 allow you to use column labels from a header row instead of column numbers. This is easier to use and understand, and also makes your LOOKUP formulas more resistant to changes such as rearrangement of the data or extra columns appearing. And you can also use this to do 2-dimensional lookup.

Built-in Error Handling

AVLOOKUP2 allows you to specify what you want returned if no exact match can be found, avoiding the need for wrapping the LOOKUP inside an IFERROR function.

The lookup column does not have to be first

You can tell AVLOOKUP2 which column to use for the lookup using a column label or a column number.

Use multiple lookup columns without requiring slow, complicated concatenation or array formulas

AVLOOKUP2 makes it simple to use multiple lookup columns (you can use a constant array {“Jess”,”Ben”} or a range of cells).

Find the first, last, Nth or all Lookup matches

AVLOOKUPNTH extends AVLOOKUP2 with an extra parameter so that you can find the first, last or Nth match when you have duplicates, for text, numbers and dates etc.
AVLOOKUPS2 returns ALL the records that match the lookup criteria. You can use AVLOOKUPS2 either as a multi-cell array formula or embedded inside an aggregating function like MAX, SUM, MEDIAN etc.

Also MATCH, Case-Sensitive and Regular Expression Lookups

The SpeedTools Lookup family also includes variations for MATCH as opposed to LOOKUP, Case-Sensitive lookups and lookups using Regular Expressions.

Here are some examples of using AVLOOKUP2 to do things that are complicated, inefficient or difficult to do with VLOOKUP:

Try it out yourself!

You can download a free 15-day trial of SpeedTools from the Decision Models website.

And you can also download a workbook VLOOKUP1.xlsx in MemLookup2.zip that contains all the examples used above.