Home/ Questions/Q 12189
Next
Answered
computing
  • 4
Asked: 2021-12-09T00:30:13+00:00 In:

Solved Vlookup Formula – Copy And Paste Not Working

  • 4

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.

Share

    1 Answer

    1. 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…C16

      If 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$11 is 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.

      • 0