Results 1 to 6 of 6

Which method is faster, VLOOKUP or MATCH & INDEX?

Threaded View

  1. #1
    Registered User
    Join Date
    10-06-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    35

    Which method is faster, VLOOKUP or MATCH & INDEX?

    I have come up with 2 ways of filling in a table with data from another sheet that gets filled via data connections.
    Do you think one of them will be less work for the CPU to process than the other?

    Setup: There is 1 column with IDs manually entered, which are the anchors for finding data on the other sheet. The formula's goal is to fill out 4 other columns.

    Method 1:
    =VLOOKUP(E5,ItemList!D5:L1400,3,FALSE)
    One of these filled down for each of the 4 columns

    Method 2:
    Add a column (F) to find the row number on the other sheet of the matching ID
    =MATCH(E5,ItemList!D5:D1400,0)+ROW(ItemList!D5:D1400)-1
    And fill the 4 columns out with a reference like this
    =INDIRECT("ItemList!E"&F5)


    TL;DR - Is 4000 VLOOKUPs faster or slower than 1000 MATCHes + 1000 ROWs + 4000 INDIRECT references?

    I would initially guess vlookups are fewer operations, but they're probably heftier. What do you think?
    Last edited by Jimmyjazz; 11-21-2013 at 05:33 AM. Reason: got nagged to give detail in title

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] More efficient (faster) sub-String method?
    By Bytor47 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2013, 08:40 AM
  2. Faster Search Method
    By boylejob in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2010, 08:35 PM
  3. Which method is more efficent/faster?
    By foxguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-02-2010, 02:53 AM
  4. faster find method
    By Baapi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-21-2007, 11:08 AM
  5. Which method is faster
    By matpoh in forum Excel General
    Replies: 2
    Last Post: 10-21-2005, 10:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1