VLOOKUP across 700K rows in 32-bit Excel

dtarockoff

New Member
Joined
Jun 26, 2013
Messages
24
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!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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)
 
Last edited:

dtarockoff

New Member
Joined
Jun 26, 2013
Messages
24
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jun 26, 2013
Messages
24

ADVERTISEMENT

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?
 

dtarockoff

New Member
Joined
Jun 26, 2013
Messages
24
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,410
Messages
5,528,613
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top