VLOOKUP across 700K rows in 32-bit Excel

[dtarockoff]

I can't apply a simple VLOOKUP across the data set I'm working with because it's so large (700K rows). I'm working out of a 32-bit excel and it's the only version I have access to.

I've saved the file as an .xlsb to try to save space, but that didn't help much. I have to apply the formula to a few thousand rows at a time currently. I'm currently working on trying to get PowerPivot installed though I'm not sure this will even help or if it's just for Pivot tables.

Has anyone navigated this issue successfully before? I've been copying and pasting the VLOOKUP results as values as I go so they don't recalculate, but that also hasn't helped.

Thanks in advance!

 

[Jonmo1]

There may be alternate solutions.

Can the data in the lookup range be sorted in Ascending Order?
Can you post some example formulas ?
Are you doing multiple columns of vlookups based on the same matching value?
i.e.
=VLOOKUP($A1,'Sheet1'$A:$Z,2,FALSE)
Drag accross to become
=VLOOKUP($A1,'Sheet1'$A:$Z,3,FALSE)
=VLOOKUP($A1,'Sheet1'$A:$Z,4,FALSE)

 

[dtarockoff]

There may be alternate solutions.

Can the data in the lookup range be sorted in Ascending Order?
Can you post some example formulas ?
Are you doing multiple columns of vlookups based on the same matching value?
i.e.
=VLOOKUP($A1,'Sheet1'$A:$Z,2,FALSE)
Drag accross to become
=VLOOKUP($A1,'Sheet1'$A:$Z,3,FALSE)
=VLOOKUP($A1,'Sheet1'$A:$Z,4,FALSE)

The lookup range cannot be sorted, it's partially in a pivot table. I can't post the actual data but the formula is in a column on a worksheet with 700K+ rows, a simple vlookup looking up the value in another column on the same worksheet within the lookup range on another worksheet that is partially a pivot table.

The lookup range starts on the pivot table and then looks up a value that's in a formula to the right of the pivot.

There is only one column of vlookup. Does that clarify things, or would a typed out example help?

 

[Jonmo1]

You have a Pivot Table with 700K+ rows ? Wow.
What does the source data look like?
Can the SOURCE data be sorted ascending?

Isn't Pivot Table row values sorted already by default?

 

[dtarockoff]

You have a Pivot Table with 700K+ rows ? Wow.
What does the source data look like?
Can the SOURCE data be sorted ascending?

Isn't Pivot Table row values sorted already by default?

Sorry I may have relayed that poorly. The source data has 700K rows. Pivot has 530K rows though, still substantial.

And you're right, the pivot table data is already sorted ascending 1-9. Are you saying it would help to also sort the source data that way?

 

[candybg]

Have you tried using INDEX MATCH instead of VLOOKUP?
Here's a page from this site with some great info:
Why INDEX MATCH is Better Than VLOOKUP
This suggests you need less processing power.
Might be worth trying on a copy of your workbook.

 

[dtarockoff]

Have you tried using INDEX MATCH instead of VLOOKUP?
Here's a page from this site with some great info:
Why INDEX MATCH is Better Than VLOOKUP
This suggests you need less processing power.
Might be worth trying on a copy of your workbook.

Haven't tried that though have read that INDEX MATCH doesn't do too much in terms of saving processing power; maybe on a data set this big it would make a difference though. I'll give it a try, thanks.

 

[Jonmo1]

Sorry I may have relayed that poorly. The source data has 700K rows. Pivot has 530K rows though, still substantial.

And you're right, the pivot table data is already sorted ascending 1-9. Are you saying it would help to also sort the source data that way?

If the Pivot Table is sorted Ascending (by the leftmost column of the lookup range)
Then Yes, that will make a big difference (you don't need to sort the source data if the pivot is already sorted)

So if this is a sample basic vlookup
=VLOOKUP(A1,'pivot table'!A$1:D$500000,4,0) <- or whatever

Use this instead

=IF(LOOKUP(A1,'pivot table'!A$1:A$500000)=A1,LOOKUP(A1,'pivot table'!A$1:D$500000),"")

YES, 2 lookups are MUCH faster than 1 VLOOKUP doing an Exact Match.