Faster Formulas to speed up slow workbook;Is Index/Match quicker to calculate vs vlookup?

Yes, Index / Match is a little quicker, as less data is loaded into memory. In your case, you are loading 8 columns into memory, to look up the 6th one. With Index / Match, you only load 2 columns. (Another benefit of Index / Match is the ability to look "left", which you can't do with Vlookup.)

So your formula could be re-written:

Formula:

Please Login or Register  to view this content.

INDEX(...MATCH()) may be slightly faster, I have not really tested it. The thing that really makes your lookup slow is the "exact match" linear search option (4th argument to VLOOKUP is FALSE, 3rd argument in Olly's MATCH() function is 0). "Approximate match" binary searches (4th argument to VLOOKUP() is TRUE or 3rd argument of MATCH() is 1 or -1) are much faster. If you can sort your lookup table on column A so that you can use a binary search option, you will realize some significant gains in the time needed for each search.

508 rows and the formula is running slowly?? Either your sheet is massively larger, or the problem lies elsewhere. If you have only 508 rows, maybe you have some awkward array formulas that are slowing you up??

Binary searches are faster, but require your data to be sorted. On massive sheets, the increase in efficiency can be really marked (not so much in the attached sample).

See https://www.powerusersoftwares.com/s...-VLOOKUP-trick


however, without seeing your sheet, it's hard to be definitive...

Instead of this sort of formula:
=VLOOKUP(A1,E2:F1600,2,FALSE)

use this sort, instead:
=IF(VLOOKUP(A1,E2:F1600,1,TRUE)=A1,VLOOKUP(A1,E2:F1600,2,TRUE),"")