Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Is there a quicker way to use Autofill and VLOOKUP to drag down 22000 rows

Posted on 2011-03-15
Medium Priority
227 Views
I am copying corresponding data from Sheet2 to Sheet1.  I am using 11 Vlookup formulas to do bring over a column at a time.  The VLOOKUP formula is as folowws:
=VLOOKUP(A4, Sheet2!\$A\$1:Sheet2!\$L\$5564, 2, FALSE)
=VLOOKUP(A4, Sheet2!\$A\$1:Sheet2!\$L\$5564, 3, FALSE)
=VLOOKUP(A4, Sheet2!\$A\$1:Sheet2!\$L\$5564, 4, FALSE)
=VLOOKUP(A4, Sheet2!\$A\$1:Sheet2!\$L\$5564,5, FALSE)
=VLOOKUP(A4, Sheet2!\$A\$1:Sheet2!\$L\$5564, 6, FALSE) etc to
=VLOOKUP(A4, Sheet2!\$A\$1:Sheet2!\$L\$5564, 12, FALSE)

I have to pull the cross at the bottom of the cell down 22000 rows.  Is there a better way to do this?
0
Question by:chaverly
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
ID: 35140208
Don't use VLOOKUP for that.
Use:
=MATCH(A4, Sheet2!\$A\$1:\$A\$5564, 0)
in one cell to get the position (say that formula is in B2) then use:
=INDEX(Sheet2!B\$1:B\$5564, \$B2)
and copy across then fill down all the formulas (double-click bottom right of final cell)
Doing the lookup part once for each row rather than 11 times should be much more efficient.

Regards,
Rory
0

Author Closing Comment

ID: 35140654
Wonderful!! Thank you so much.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month8 days, 4 hours left to enroll