CODEWITHSUNDEEP
excelvbaperformance
Alex Ritter
Alex Ritter

Reputation: 1041

Speed up Index/Match in Excel Formula for 65,000 queries

I'm attempting to run an index/match query for 65,000 cells as part of a store inventory calculation in Excel. We have 65,000 unique items in our database.

Anyways, here is the formula I am pasting down a single column for 65,000 rows. Obviously, it runs EXTREMELY slow. What could I possibly change to speed things up?

=INDEX(SAQTY!H:H, MATCH(A2&"GRA", SAQTY!C:C&SAQTY!F:F, 0))

On a side note, the index/match is cross checking across multiple sheets, does that have anything to do with performance?

Upvotes: 0

Views: 2170

Answers (1)

Scott Craner
Scott Craner

Reputation: 152475

make the references dynamic like this:

=INDEX(SAQTY!H:H, MATCH(A2&"GRA", SAQTY!C1:INDEX(SAQTY!C:C,MATCH(1E+99,SAQTY!H:H))&SAQTY!F1:INDEX(SAQTY!F:F,MATCH(1E+99,SAQTY!H:H)), 0))

Upvotes: 3

Related Questions