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

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?
###### Who is Participating?

Commented:
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 Commented:
Wonderful!! Thank you so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.