VLOOKUP: Change col_index_num when copying to other columns?

[lamboe80]

I use VLOOKUP every day in my job. One thing that has always bugged me about the VLOOKUP function is that I have to manually change the column number that I want to lookup when I'm copying the formula across several columns. I do analysis on data in months, so I never have to change more than 12 columns at a time. Today, I'm having to analyze data in weeks, and will probably be doing this more in the future. I don't want to have to change the column number 52 times to retrieve a year's worth of data from another worksheet. Is it possible to embed another formula in the col_index_num so that the lookup column number will change as I copy it across the columns?

Thanks in advance.

 

[Jonmo1]

You can replace the column reference with a formula to calculate it...

using the column function for one example...

if the formula is entered in Column M for example..also known as 13.
and you write your first formula like this...

=vlookup(a1,A:L,2,false)
and you want to fill right to make it look like
=vlookup(a1,A:L,3,false)

right??

In column M, you would write

=vlookup($A1,$A:$L,column()-11,false)

if written in column M, column(13)-11- = 2
when moved to column N, it becomes column(14)-11- = 3
when moved to column O, it becomes column(15)-11- = 4
etc...

hope this helps

 

[Richard Schollar]

Yep:

=VLOOKUP(myCell,myRange,COLUMNS($A$1:A$1),0)

as you copy this across from wherever you enter the formula, it will progressively increment the column number.

Hope this helps!

 

[lamboe80]

Wow, guys, this is so easy and fast! Thank you very much. I searched google and this site for 30 minutes and couldn't find anything.

Thanks again. You nailed it.

 

[pradeepkumarbe]

Nice Man..Thank You