Page 3 of 3 FirstFirst 1 2 3
Results 21 to 27 of 27

Thread: INDEX+MATCH versus VLOOKUP

  1. #21


    Register for a FREE account, and/
    or Log in to avoid these ads!

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





    Quote Originally Posted by oliver View Post
    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.

  2. #22
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  3. #23
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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.

  4. #24
    Seeker AnalystCave's Avatar
    Join Date
    Dec 2015
    Location
    Poland
    Posts
    11
    Articles
    0
    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.

  5. #25
    Thanks for the help guys..

  6. #26
    It was really helpful

  7. #27
    Seeker AnalystCave's Avatar
    Join Date
    Dec 2015
    Location
    Poland
    Posts
    11
    Articles
    0
    Quote Originally Posted by nishatian1 View Post
    It was really helpful
    You can check out the analysis here:
    http://www.analystcave.com/excel-vlo...l-performance/

Page 3 of 3 FirstFirst 1 2 3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •