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

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

Hi,

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.

/René

2. ## 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:
`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.

3. ## 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. ## Re: INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write

You're welcome.

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

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