+ Reply to Thread
Results 1 to 4 of 4

INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?

  1. #1
    Registered User
    Join Date
    11-02-2016
    Location
    Sweden
    MS-Off Ver
    2013
    Posts
    21

    INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?

    Hi,

    I just received some great help adjusting my code: =IFERROR(VLOOKUP(A3;'[Testbook.xlsm]Sheet1'!$C:$BW;MATCH($J$2;'[Testbook.xlsm]Sheet1'!$C$2:$BW$2;0);0);0).

    I use the above code to look through a pivot table in another workbook and return the value if VLOOKUP and MATCH finds a match, if not the value returned is 0 instead of N/A, that's the part I got help with using IFERROR.
    While trying to find a solution myself I came across a lot of posts and guides saying that INDEX+MATCH is a better and quicker and safer formula to use then VLOOKUP+MATCH.

    I work with 30-40 workbooks containing thousands of rows and up to 60-70 columns that have this kind of formula in them gathering information from other workbooks.

    Can anyone explain why INDEX is better then VLOOKUP?

    And if it is better, safer and faster, what would the code look like?

    When trying to use INDEX I couldn't code to using match values like above.


    Thanks in advance

    /René

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write

    Hi,

    I would suggest that using GETPIVOTDATA is probably better for returning values from a pivot table.

    INDEX/MATCH is typically slightly faster than an equivalent VLOOKUP formula but the real benefit occurs when you are returning data from several columns for the same lookup value. With VLOOKUP, you have to lookup the value each time, which is inefficient; with INDEX/MATCH you can perform the MATCH part once in a separate column and have several INDEX formulas that refer to the MATCH result directly.

    You could rewrite your formula as
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you are copying this formula down, it would be better to put the MATCH($J$2;'[Testbook.xlsm]Sheet1'!$C$2:$BW$2;0) part into a separate cell at the top of the column and refer to that.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    11-02-2016
    Location
    Sweden
    MS-Off Ver
    2013
    Posts
    21

    Re: INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write

    Thank you so much for the response.

    Both codes seems to work equally well. I will run them in two different workbooks to see if results are consistent.

    I have been looking at GETPIVOTDATA as well. Maybe I have been reading in the wrong places but it doesn't feel as dynamic as above code. It probably is if coded correctly.

    Thanks again for your response, explanation and solution to my question.

    /René

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 06-18-2015, 08:45 AM
  2. Replies: 3
    Last Post: 05-19-2014, 02:01 PM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  5. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  6. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  7. Replies: 2
    Last Post: 03-16-2012, 12:03 PM

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