Formula will not increment

  • Thread starter Thread starter Barney
  • Start date Start date
B

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
 
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))
 
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
 
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))
 
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
 
=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
Click to expand...
 
Back
Top