Structured Table Referencing and VLOOKUP

Way back when, I wrote a post about naming columns that turned into a post about VLOOKUPs and MATCHes using structured table references. As usual, there was gold in them there comments. Since then I’ve been using the COLUMN() function in my VLOOKUP formulas to identify the return column. Like this:

This has the restriction that your table start in A1. Mine do. One table per worksheet and it starts in A1. This has been working well for me, but I’m sick of typing the COLUMN part. So I made a macro and here it is.

The IsTableVlookup function needs a little help. I just kept adding conditions as I tested it (and broke it), but I should probably just rethink the whole thing. Or maybe not, we’ll see. With this little gem in my custom class that handles application events, I can type

and it will insert the COLUMN part for me. I could even modify it for tables that don’t start in A1 by subtracting the COLUMN of the first column. The function also assumes you prefix all of your tables with “tbl”. You can change to suit. If you don’t use a naming convention, then I guess you’ll have to loop through all the tables and see if it matches one.

In an effort to be more like Debra, I made a video. As with every video I record with CamStudio, the first few seconds are blurry. I don’t know why.

VLOOKUP & INDEX/MATCH Are Both Badly Designed Functions: Here Are Some Better Ones

It’s fun to argue about whether VLOOKUP or INDEX/MATCH is better, but to me that’s missing the point: they are both bad.

So I decided to design and build some better ones.


Here are some of the more-frequently mentioned VLOOKUP INDEX/MATCH problems

  • Slow exact match (linear search)
  • Approximate sorted match is the wrong default 99.9% of the time and gives the wrong answer without warning
  • Cannot do exact match on sorted data (well they can but only if they ignore sorted!)
  • Numeric VLOOKUP answer column easy to break
  • No built-in error handling for exact match
  • VLOOKUP very inflexible
  • INDEX/MATCH more flexible but still limited
  •  …

MEMLOOKUP/MEMMATCH – easier and faster alternatives to VLOOKUP/MATCH

MEMLOOKUP ( Lookup_Value, Lookup_Array, Result_Col, Sort_Type, MemType_Name, Vertical_Horizontal )

The syntax is designed to make it easy to convert a VLOOKUP to MEMLOOKUP, but there are differences!

  • Defaults to Exact Match on both unsorted and unsorted data
  • Use either column labels or numbers
  • Fast exact match on both unsorted and sorted data
  • Automatic optimisation of multiple lookups within the same row

So you want more flexibility? Try the AVLOOKUP/AMATCH family of functions

It’s always tempting to cram in more function (scope creep is universal), but if the result is too many parameters then it’s a mistake. So instead there is a whole family of these lookup functions that build on the MEMLOOKUP/MEMMATCH technology to provide the ultimate in flexibility and power whilst remaining efficient.

  • Lookup using any column
  • Lookup using more than one column without slow concatenation
  • Lookup the first, last, Nth or all results on both sorted and unsorted data
  • Lookup both rows and columns (2-dimensional lookup is built-in)
  • Built-in error handling for exact match
  • Return multiple answer columns
  • Case-sensitive lookup option
  • Regex match option


Try them out for yourself

These functions are included in the 90 or so additional Excel functions built into FastExcel V3.
You can download the trial version from here.

Download FastExcel V3

If you like them then ask Microsoft to add them to the next version of Excel!

I would be delighted to tell the Excel team how I built these functions and the algorithms they use.

By the way they are written as C++ multi-threaded functions in an XLL addin for maximum performance.