PDA

View Full Version : Solved: VLOOKUP AND COLUMN FUNCTION

Tenspeed39355
06-08-2008, 10:58 AM
Good afternoon guys This one has me stopped.
I am using the VLookup command to retreive data from Sheet2 and put the data in sheet1. I can put a hold on everything in the VLookup command using the \$ sign. How do I change the column function so the
VLookup command moves from column to column?
Example. In sheet1 Column B I have =VLookup*\$A\$,Sheet2!\$A\$2:\$C\$50,2,0) When I move across the the row how do I get the column command to go from 2, to 3 and 3 to 4 and 4 to 5?? I have a lot of columns to cover so is there a way to advance the column command
as I move across the ss?? Thanks for any help with this.
Max

mdmackillop
06-08-2008, 12:17 PM
Instead of using 2 as the offset, use Column() plus/minus X to suit.

Tenspeed39355
06-08-2008, 01:10 PM
The data is in sheet2. The VLookup fuction is in Sheet1.
The first VLookup is in Sheet1 B2.
I will be going from B2 thru Z2.
The Column fuction has to change 52 times.
Example: =Vlookup(\$A\$1,Sheet2!\$A\$2:\$Z\$2,2,0)
=Vlookup(\$A\$1,Sheet2!\$A\$2:\$Z\$2,3,0)
=Vlookup(\$A\$1,Sheet2!\$A\$2:\$Z\$2,4,0)
Everything stays the same except the Column function changes each time.
The last Column function will be 26
If you show me how to get started I can go to 100 if I wish.
Max

mdmackillop
06-08-2008, 01:52 PM
=Vlookup(\$A\$1,Sheet2!\$A\$2:\$Z\$2,column()+1,0)
Just change the +1 to suit your relative column positions.

Tenspeed39355
06-09-2008, 05:04 AM
MDMACKILLOP Thanks, you hit that one on the head. The formula
works great. You guys have helped me out of many jams.
Keep up the good work.:friends:
Max