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

### VLOOKUP INDEX/MATCH problems

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