INDEX+MATCH versus VLOOKUP

**Daniel Ferry** · 2011-06-15, 06:40 PM

If you are considering using INDIRECT, then this is going to be way slower than a direct VLOOKUP.

Originally Posted by oliver

Hey, sorry quick question:

Which is faster,

1) a Linear Search Vlookup over a range, or
2) a Binary Search Match + an Indirect reference over the range?

Context/Example:

I'm trying to test whether a date is in a list of dates. My first option is VLOOKUP(Date, Range, 0). My second option is Indirect("LISTNAME" & MATCH(Date, Range, 1)) = Date. I'm wondering which one's faster.

**Roger Govier** · 2011-06-15, 07:54 PM

Hi Oliver

Welcome to the forum.
As Daniel has said, anything with Indirect is going to be slower than a direct method.

But rather than Vlookup, I think you will find Countif is faster.
With your dates in column A and the date being tested in B1
=COUNTIF(A:A,B1)>0

**JeffreyWeir** · 2015-03-25, 01:23 PM

There's another important point in the INDEX/MATCH combo's favor that has been overlooked in this thread: and that's this bit in bold
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The fact that VLOOKUP functions point at an entire table - even though they only actually return data from one column - means that any time you change any cell in that table, your VLOOKUP is going to recalculate. And then so are ALL formulas downstream of those VLOOKUPS. It's almost as if VLOOKUP is a volatile function, where changes to that that Table is concerned.

Whereas with INDEX/MATCH, your functions will ONLY recalculate if you change something in the specific columns that the INDEX and MATCH functions actually reference.

Not to mention that inserting columns in the middle of your table breaks your VLOOKUP, whereas INDEX/MATCH is resistant to column insertions.

I'll take INDEX/MATCH any time.

**AnalystCave** · 2015-12-01, 01:26 PM

Old thread but can't help to share my two cents:
I have compared VLOOKUP, INDEX-MATCH, SQL and VBA head on ranging from 25k to 200k lookups

My key takeaways:

Sorted data:
- DOUBLE TRUE VLOOKUPs rule performance wise
- For similar performance and best practice better to use TRUE VLOOKUP + APPROXIMATE INDEX MATCH combo

Unsorted data:
- Usage of TRUE VLOOKUP / APPROXIMATE INDEX MATCH not possible
- MS Query (SQL) & VBA rule in terms of performance. SQL better as no macro's required (file can be save as XLSX)

For me it's TRUE VLOOKUP + APPROXIMATE INDEX MATCH all the way.