vlookup only grabbing certain columns

[ksbrace]

I am trying to grab some columns from Sheet 2 based on the filenumber (which is on both sheets).

this is what my Sheet 2 looks like. There are more columns but just so you see the first few...

filenumber, name_first, name_last, name_suffix, street_address, p_city_name, st, z5, z4

my columns have numbers, not letters(for whatever reason).
this is in column 34 on Sheet 1:

=VLOOKUP(C[-33],Sheet2!C[-33]:C[16],2,FALSE)

these are my results:

JOHN	#N/A	0	#N/A	#N/A	IA	50428	2510

I have no idea why it's grabbing the following and nothing else:
name_first, name_suffix, st, z5, z4

Any help would be greatly appreciated..

 

[SkipVought]

Hi,

In your Excel Options there's a place to change the column designation.

 

[SkipVought]

Also your references are all honked up!

You are using R1C1 reference style AND relative referencing.

You need to change that Excel Option.

Then check your VLOOKUP() formula and post back what that is.

 

[ksbrace]

I changed that in Excel Option and it's now only grabbing the first value

=VLOOKUP(CG2,'Sheet 2'!$CD:$CP,2,FALSE)

When I move to the next column, I can do this:

=VLOOKUP(CG2,'Sheet 2'!$CD:$CP,3,FALSE)

=VLOOKUP(CG2,'Sheet 2'!$CD:$CP,4,FALSE)

...etc. but I have to keep putting CG2 back in and incrementing the colummn number manually.

I thought column()-2 would work...

 

[SkipVought]

Enter 2 in the first column containing your VLOOKUP() in row 1.

SELECT that cell in row 1 and drag the Fill Handle across until you have 12 sequential numbers.

Assuming that 2 is in B1...

[tt]
=VLOOKUP($CG$2,'Sheet 2'!$CD:$CP,B$1,FALSE)
[/tt]
SELECT your formula and drag the Fill Handle across.

 

[SkipVought]

Use $CG2 as you lookup value reference.