Pasting Vlookup Formula

[dylbertiii]

Hey all,
First time posting here. I think i've done a pretty good job at searching and scouring previous posts to make sure this problem is an original one; however, if you know of relevant post to this that i missed, please let me know.

I'm using Excel 2011 for Mac. I'm working with a relatively large file (just under 60 mb). I've thinned it down about as far as i can to about 20 columns. There are 800k+ rows. I'm linking from another larger file and using the VLookUp function for one column's data and will need to do so for about 4 or 5 more columns. I'm still working on the first one and it's giving the results i want it to until i try copying and pasting. If i drag the paste down from the original cell, the formula pastes just fine, but a drag for 800k+ rows would take quite a bit of time. Likewise, if i copy the formula'd cell and then scroll down even 5,000 rows, hold shift and click, and then paste, the formula pastes just fine. Of course the easiest thing would be to copy the desired cell, hit command + down to navigate to the bottom most row, hold shift and click that cell in the bottom most row, and then paste, but it results in pasting the value in all selected cells! It's got me quite flustered. I've checked my calculations and they're still set to automatic before and after i paste.

Is there anything i can do? Or is my best bet to copy, scroll, shift and click, and paste? That's probably what i'll be doing until i find a better solution.

Thanks in advance!

 

[dylbertiii]

You might consider Index/Match instead of Vlookup. It's generally MUCH more efficient.

Can you post an example of the formula?

I'll research Index/Match as well. (All this, even VLookUp, is new to me.)

Example: =VLOOKUP(E745230,'Macintosh HD:Users:[username]:Desktop:[tourist id d.xlsx]Sheet1'!$D$2:$E$1048576,2,FALSE)

 

[Jonmo1]

Now is that formula then copied accross to multiple columns?
Like say
=VLOOKUP(E745230,'Macintosh HD:Users:[username]:Desktop:[tourist id d.xlsx]Sheet1'!$D$2:$E$1048576,2,FALSE)
Then
=VLOOKUP(E745230,'Macintosh HD:Users:[username]:Desktop:[tourist id d.xlsx]Sheet1'!$D$2:$F$1048576,3,FALSE)
=VLOOKUP(E745230,'Macintosh HD:Users:[username]:Desktop:[tourist id d.xlsx]Sheet1'!$D$2:$G$1048576,4,FALSE)
etc

 

[dylbertiii]

Now is that formula then copied accross to multiple columns?

The two is constant. That's not the array column, is it? That's actually probably the D$2 huh?

In fact, everything is constant after Sheet1'...

Smells funny...

 

[Jonmo1]

hmm, too bad.

Is the data in Sheet1!D2:D1048576 Sorted Ascending?
If not, can you make it that way?

 

[dylbertiii]

Far as i know, it already is.

Originally i had numbers to id a household. I gave it a formula to add two sets of two digits each at the end of the house id number to make a distinct id value based on each house id and two more values from two different columns.

Examples from the first rows are as follows:
200000170101 200000170102 200000170103 200000170104 200000170105 200000170201 200000170202 200000170203 200000170204 200000170205 200000170206 200000170207 200000170208 200002310101

I can explain that in greater detail if needed, but i think all you need to know is that all values within the reference column should be unique and in ascending order.

Should i put a sort on it anyway?

 

[Jonmo1]

If the data in column D is sorted ascending, try

=LOOKUP(E745230,'Macintosh HD:Users:[username]:Desktop:[tourist id d.xlsx]Sheet1'!$D$2:$E$1048576)

This will be FAR more efficient.

And if there are non exact matches (#N/A results from the original vlookup), do this

=IF(LOOKUP(E745230,'Macintosh HD:Users:[username]:Desktop:[tourist id d.xlsx]Sheet1'!$D$2:$D$1048576)=E745230,LOOKUP(E745230,'Macintosh HD:Users:[username]:Desktop:[tourist id d.xlsx]Sheet1'!$D$2:$E$1048576),NA())

I know, it seems like that would be worse by having 2 lookups.
But 2 lookups using "closest match" type, is still FAR faster than 1 VLOOKUP using "Exact Match"

 

[Fazza]

Some other ideas.

Put the data in a database instead of spreadsheet is probably best.

If staying with a spreadsheet don't use formulas (at all). Instead use database type approach. Such as UPDATE query to replace the lookups. This would also avoid concatenating multiple fields to make a unique ID. The query can handle it directly from the base fields without a manufactured key.