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?

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.

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)

AMAZING!

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

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).

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.