# Dragging Formula Down Rows, Changing Array Reference Across Columns

1. ## Dragging Formula Down Rows, Changing Array Reference Across Columns

I am using VLOOKUP to automatically fill in data in one column based on the selection from a drop down menu in another.

The DATA is located on another sheet.

I am trying to drag the formula down and want to shift the array used in my VLOOKUP over 3 by columns for every row I drop down.

A1 =VLOOKUP(\$S6, Invoices!B\$4:D\$9, 2, 0)

A2 =VLOOKUP(\$S7, Invoices!E\$4:G\$9, 2, 0)

How can I rewrite the code in cell A1 so that when dragged to cell A2 it would have the desired effect?

2. ## Re: Dragging Formula Down Rows, Changing Array Reference Across Columns

Try this

=VLOOKUP(\$S6,INDEX(Invoices!B\$4:ZZ\$4,1,ROW(A1)*3-2):INDEX(Invoices!D\$9:ZZ\$9,1,ROW(A1)*3),2,0)

change the ZZ reference to the last column of your data on Invoices sheet

3. ## Re: Dragging Formula Down Rows, Changing Array Reference Across Columns

Originally I attempted to go this route:

= VLOOKUP(\$S6, !Invoices\$B\$4:\$AX\$4, ROW(A1)*3-1, 0)

However this does not work as the VLOOKUP function searches for the value from \$S6 only in the first column of the selected array.

4. ## Re: Dragging Formula Down Rows, Changing Array Reference Across Columns

Have you tried the formula I posted (and edited)?

=VLOOKUP(\$S6,INDEX(Invoices!B\$4:ZZ\$4,1,ROW(A1)*3-2):INDEX(Invoices!D\$9:ZZ\$9,1,ROW(A1)*3),2,0)

5. ## Re: Dragging Formula Down Rows, Changing Array Reference Across Columns

AMAZING!

This would've consumed my soul all week. Thank you so much

6. ## Re: Dragging Formula Down Rows, Changing Array Reference Across Columns

You're welcome. Glad to help a fellow Torontonian (well I used to be anyway). Don't forget to mark your thread as SOLVED (click on Forum Rules @ top of page for instructions if needed).

7. ## Re: Dragging Formula Down Rows, Changing Array Reference Across Columns

Hi Cutter,

Can I ask a question along the same lines as this post? I also have a VLookup function that I would like to copy/drag 30 rows or so. For example:

A1 =VLOOKUP(\$G\$18,Stored!A:PK,2,FALSE)

A2 =VLOOKUP(\$G\$18,Stored!A:PK,3,FALSE)

A3 =VLOOKUP(\$G\$18,Stored!A:PK,4,FALSE)

A4 =VLOOKUP(\$G\$18,Stored!A:PK,5,FALSE)

and so on. Anyway to get the column number to change with a drag? I thought without the "\$" it would automatically create the series. I'm wrong. haha. I'm guessing it doesn't apply to column numbers. Thanks for the help.

There are currently 1 users browsing this thread. (0 members and 1 guests)