Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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?
1 Solution
Rory ArchibaldCommented:
Don't use VLOOKUP for that.
=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.

chaverlyAuthor Commented:
Wonderful!! Thank you so much.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now