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

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

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.

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

This entry was posted in Calculation, Lookups, UDF, XLL and tagged . Bookmark the permalink.

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

1. sam says:

Charles,

The below formula array entered performs a binary search on a range containing unsorted data to determine the position of an exact match of the searched Item

=Match(1,1/(RngToSearch=WhatToSearch))

On a range of unsorted data containing duplicates the below formula array entered performs a High Low binary search to return an array pair containing the position of the first and the last instance of the searched item

=MATCH({1,2},1/(RngToSearch=WhatToSearch),{-1,1})

If the data is sorted then the formula can be simplified further

=MATCH(1,1/(RngToSearch=WhatToSearch),{-1,1})

Both the above formulas can be used to do range slicing on large data to reduce the range to search

In all the above formulas the term 1/(RngToSearch=WhatToSearch) returns an array of #DIV!0 or 1’s

The Binary search options of Match are blind to this error value and see only an array’s of 1 so always see “pseudo sorted” data

The problem is in Excel the operation 1/(RngToSearch =WhatToSearch) is slow on large data

Do you know of an alternate faster method of generating the Array of #DIV!0 or 1’s …..

What algorithm do the fastexcel functions follow on unsorted data for performing an exact match.
On sorted and unsorted data containing duplicates which fastexcel function can return an array pair of the First and Last instance of the searched term ?

• fastexcel says:

Hi Sam,
Thats very clever: I have not seen the hi-lo trick before.
But as you say it is very slow.

The closest SpeedTools equivalents are either

2 AMATCHNTH formulas, one finding the first and the second finding the last,

or a better solution is FILTER.MATCH which returns start row index and number of consecutive rows for each cluster of consecutive matches so that its easy to use OFFSET to get the filtered subsets.

Both of these are much faster (about 30 times faster on a test over 70K rows) than your clever MATCH array formula.
And FILTER.MATCH allows for multiple criteria matching like SUMIFS but more powerful.

The FastExcel SpeedTools functions use Linear Search on unsorted data, but preceded with an indexed Lookup Memory search where appropriate.