I am using a vlookup to retrieve bonus for employees based on years of service. The problem is when I copy and paste the original formula, it does not change the column index but instead gives me a REF# error. How do I get the column index number to change when I copy and paste. The original formula had 2 for the values from column 2. The formula I am using is:

=VLOOKUP(A2,Sheet2!$A$1:$C$6,2,FALSE)

It gives me the correct return value from column 2 but when I copy and paste the formula in the next cell (horizontally, to the right of the original formula), I want it to give me the return value from column 3 and as I drag it across, it should give mt the values from column 4, then 5, etc. How do I get this to work. I know it is something simple. Thanks in advance for your help.

✔ Best Answer

If you do not get it to work after this post, please tell me what cell(s) you are using for the VLOOKUP formula. The syntax of your latest attempt is wrong:

=VLOOKUP(C16,'Pivot-DL$ (A1)'!$A$7:$E$11,COLUMN(),-2)

Let's break the VLOOKUP arguments down piece by piece.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:

C16

table_array:'Pivot-DL$ (A1)'!$A$7:$E$11

col_index_num:You want 2, more on this later

[range_lookup]:-2?The range_lookup argument should only be FALSE (0) or TRUE (1) A range_lookup argument of -2 is going to give you invalid results.

First, let's discuss your lookup_value... value...

C16If you are dragging this formula across columns, you have to lock the lookup_value also or it will increment from C16 to D16 to E16 as you drag it across.

If you always want to lookup C16, you have to use $C16 which will lock the Column but allow the Row to increment if you are also dragging it down.

Second, your table_array...

I can only assume that

'Pivot-DL$ (A1)'!$A$7:$E$11is a valid table_array for your workbook. Since I can't see your workbook from where I'm sitting, I'll have to trust you that it is a valid table_array.Third, your col_index_num...

Did you try any of the tests I suggested as a means to familiarize yourself with the COLUMN function?

I don't have any other way to explain it other than it simply returns the Column number of the Column it is used in.

If you put this in D16, it will return 4, because D is the 4th Column on a worksheet.

=COLUMN()

If you put this in D16, it will return 2, since 4 - 2 = 2

=COLUMN()-2

Therefore, if you put this in D16:

=VLOOKUP(A2,Sheet2!$A$1:$C$6,COLUMN()-2,FALSE)

it will be equivalent to:

=VLOOKUP(A2,Sheet2!$A$1:$C$6,2,FALSE)

and

=VLOOKUP(A2,Sheet2!$A$1:$C$6,2,0)

Finally, your range_lookup argument...

As I said earlier, this has to be 0 (FALSE) or 1 (TRUE) or omitted, which is the same as TRUE. Since I assume you are looking for an exact match, you want the range_lookup argument to be 0 (FALSE).

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Since the col_num argument is just a number and not a reference, Excel has no reason to increment it when you drag it across. You need to use a function that Excel wants to update.

Replace the 2 with the COLUMN function and it will increment as you drag it. The COLUMN function does nothing more than return the Column number in which it resides.

=VLOOKUP(A2,Sheet2!$A$1:$C$6,COLUMN(),0)

You can perform mathamatical operations on the COLUMN function to make sure it returns the actual number you need.

e.g. If your first VLOOKUP resides in Column C (3) and you need a col_num argument of 2, simply subtract 1:

=VLOOKUP(A2,Sheet2!$A$1:$C$6,COLUMN()-1,0)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

First, I used: =VLOOKUP(C17,'Pivot-DL$ (A1)'!$A$7:$C$13,COLUMN()-1,0) When I did this, instead of returning the value in column 2, it returned the value in column 3 vice column 2. Then when I dragged the formula across the rows for each subsequent month to get the return value for that column, I got an REF# error.

So, I changed the formula to =VLOOKUP(C17,'Pivot-DL$ (A1)'!$A$7:$C$13,COLUMN(3)-1,0)

I thought by doing this , it would tell Excel that I wanted the value in column 2 (3-1 ) but that did not work eitherPlease tell me what I am doing wrong. Thank you.

You didn't mention which column your VLOOKUP formula is in, so I could not give you the exact formula to use. That is why I tried to answer your question in a generic manner. As I said, the COLUMN function returns the column number in which it resides. It does not take any arguments, so COLUMN(3) won't work.

To understand how the COLUMN function works, try entering =COLUMN() in any cell. It will simply return the Column number of the column in which you put it.

In A1, =COLUMN() will return 1

In I45, =COLUMN() will return 9

In FZ16, =COLUMN() will return 182

etc.As I said, since the COLUMN function returns a number, you can perform just about any mathematical operation on it.

In A1, =COLUMN()-1 will return 0

In I45, =COLUMN()*5 will return 45

etc.Therefore, you first need to know the Column number in which you are using your first VLOOKUP and then subtract (or add) a specific number to the COLUMN function so that it returns the value you are looking for.

Since your use of COLUMN()-1 returned a "3", I'm going to guess that your first VLOOKUP is in Column D, since 4 - 1 = 3.

If your first VLOOKUP is indeed in Column D, then you need to use COLUMN()-2 since 4 - 2 = 2.

BTW, the ROW function does the same thing for Rows and can be used in the same manner.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DerbyDad03, Thank you for your patience but something is not right. Here is what I entered this time:

=VLOOKUP(C16,'Pivot-DL$ (A1)'!$A$7:$E$11,COLUMN(),-2)

C16 = employee ID I want to look up the bonus amount for

'Pivot-DL$ (A1) = the worksheet that contains the employees ID, years of service, bonus amount

$B$7:$E$11 - the the column numbers for my VLOOKUP.

Since I want the valuse for column C first, I entered a -2However, it keeps giving me for column E. Even if I change the column number, I get the same result. I know it must be something simple I am missing, but I do not know what that is.

If you do not get it to work after this post, please tell me what cell(s) you are using for the VLOOKUP formula. The syntax of your latest attempt is wrong:

=VLOOKUP(C16,'Pivot-DL$ (A1)'!$A$7:$E$11,COLUMN(),-2)

Let's break the VLOOKUP arguments down piece by piece.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:

C16

table_array:'Pivot-DL$ (A1)'!$A$7:$E$11

col_index_num:You want 2, more on this later

[range_lookup]:-2?The range_lookup argument should only be FALSE (0) or TRUE (1) A range_lookup argument of -2 is going to give you invalid results.

First, let's discuss your lookup_value... value...

C16If you are dragging this formula across columns, you have to lock the lookup_value also or it will increment from C16 to D16 to E16 as you drag it across.

If you always want to lookup C16, you have to use $C16 which will lock the Column but allow the Row to increment if you are also dragging it down.

Second, your table_array...

I can only assume that

'Pivot-DL$ (A1)'!$A$7:$E$11is a valid table_array for your workbook. Since I can't see your workbook from where I'm sitting, I'll have to trust you that it is a valid table_array.Third, your col_index_num...

Did you try any of the tests I suggested as a means to familiarize yourself with the COLUMN function?

I don't have any other way to explain it other than it simply returns the Column number of the Column it is used in.

If you put this in D16, it will return 4, because D is the 4th Column on a worksheet.

=COLUMN()

If you put this in D16, it will return 2, since 4 - 2 = 2

=COLUMN()-2

Therefore, if you put this in D16:

=VLOOKUP(A2,Sheet2!$A$1:$C$6,COLUMN()-2,FALSE)

it will be equivalent to:

=VLOOKUP(A2,Sheet2!$A$1:$C$6,2,FALSE)

and

=VLOOKUP(A2,Sheet2!$A$1:$C$6,2,0)

Finally, your range_lookup argument...

As I said earlier, this has to be 0 (FALSE) or 1 (TRUE) or omitted, which is the same as TRUE. Since I assume you are looking for an exact match, you want the range_lookup argument to be 0 (FALSE).

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll