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
2
Medium Priority
?
227 Views
Last Modified: 2012-05-11
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
Comment
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
  • Learn & ask questions
2 Comments
 
LVL 85

Accepted Solution

by:
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

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question