Formula will not increment

[Barney]

In Excel 2002 my formula is "=IF($Z$8="","",VLOOKUP($Z$8,$Z$32:$CL$60,15))".
When I drag and copy this cell to other columns to the right, I want the 15
to change to 16, 17, etc. It is copying okay but the 15 does not change.
What am I doing wrong?

Thanks,

Barney

 

[Dave Peterson]

I'd use the function =column() and then adjust that to get to 15.

For instance, if the formula is in column A, then =column() would return 1 and
I'd have to add 14.

=IF($Z$8="","",VLOOKUP($Z$8,$Z$32:$CL$60,column()+14))

 

[Barney]

The formula is in Column N (14) while the number I am looking for (385) is
in Column AN (40). So I do column()+26. It returns "0".

Barney

 

[Dave Peterson]

If the your initial formula in column N worked with:
=IF($Z$8="","",VLOOKUP($Z$8,$Z$32:$CL$60,15))
Then
=IF($Z$8="","",VLOOKUP($Z$8,$Z$32:$CL$60,column()+1))
should work.

If 385 is in Z8, then the number you're matching on doesn't change the argument
that describes the column to bring back.

And remember, if you want an exact match, you'd specify 0 or false as the 4th
argument:

=IF($Z$8="","",VLOOKUP($Z$8,$Z$32:$CL$60,column()+1,false))

 

[Barney]

Dave,

=IF($Z$8="","",VLOOKUP($Z$8,$Z$32:$CL$60,column()+1)) worked just fine. I
am not sure what you are saying about the 4th arguement.

Thanks for your great help.

Barney

 

[Dave Peterson]

=vlookup() can have up to 4 arguments.

=vlookup(value,lookuprange,column,trueORfalse)

If the last argument is false (or 0), then that means you want an exact match
between the value and the first column in the lookuprange.

If there is no match, then the formula will evaluate to an error (#n/a).

If you don't need an exact match, you can specify True for the fourth argument.
You don't have to specify True since that's the default.

Dave,

=IF($Z$8="","",VLOOKUP($Z$8,$Z$32:$CL$60,column()+1)) worked just fine. I
am not sure what you are saying about the 4th arguement.

Thanks for your great help.

Barney