Vlookup and autofill

[Guest]

Can i use autofill to increase the column count it looks in?
On row 1 for example I have =vlookup($b3,sheet1!$a:$cg,5,0) and i then want
to increment it by 3 in the next column
i.e. =vlookup($b3,sheet1!$a:$cg,8,0) and then
=vlookup($b3,sheet1!$a:$cg,11,0) and so on.

Thanks in your advance for your help

 

[Guest]

As a P.S.

I have tried replacing the 5 at the end with COLUMN(E1),0 but the auto fill
only increases the increment by 1

Thnaks

 

[Guest]

That used to bug me as well.

What you need to do is set up something so that an autofill will work across
as well as down.

So instead of
=vlookup($b3,sheet1!$a:$cg,5,0)

you will have
=vlookup($b3,sheet1!$a:$cg,c$1,0)

drag this across and down and it will be d$1, e$1 etc etc

then put value 5 in cell d1, and in cell e1 put =d1+3, and drag that across
to the right.

HTH

 

[Guest]

Excellent, I will give that a blast

thanks

That used to bug me as well.

What you need to do is set up something so that an autofill will work across
as well as down.

So instead of
=vlookup($b3,sheet1!$a:$cg,5,0)

you will have
=vlookup($b3,sheet1!$a:$cg,c$1,0)

drag this across and down and it will be d$1, e$1 etc etc

then put value 5 in cell d1, and in cell e1 put =d1+3, and drag that across
to the right.

HTH

 

[Don Guillett]

try a helper column 5, 8 11
vlookup($b3,sheet1!$a:$cg,c1,0)

 

[Guest]

That worked a treat thanks guys.

Regards

Chris