Ask Your Question

How to stop array reference changing when copying VLOOKUP formula?

asked 2015-10-07 17:21:21 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Libreoffice calc on Ubuntu 14.10

I have a VLOOKUP formula that when I copy and paste down the column, I get

=VLOOKUP(A3, G3:I80, 3, 0)
=VLOOKUP(A4, G4:I81, 3, 0)
=VLOOKUP(A5, G5:I82, 3, 0)
=VLOOKUP(A6, G6:I83, 3, 0)

The array reference, the 2nd parameter, changes as I go down the column. I need the array reference to stay the same, like

=VLOOKUP(A3, G3:I80, 3, 0)
=VLOOKUP(A4, G3:I80, 3, 0)
=VLOOKUP(A5, G3:I80, 3, 0)
=VLOOKUP(A6, G3:I80, 3, 0)

otherwise, obviously, the formula will be looking up to a different array of data in each cell.

How can this be done?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-10-11 18:57:38 +0100

Alex Kemp gravatar image

updated 2015-10-11 19:02:24 +0100

Hi mlpqlm

You can use a '$' (U+0024) DOLLAR SIGN to instruct Calc not to rewrite the references. Each cell name is in 2 parts (ignoring the Sheet name), so there are 4 options:-

  • G3 = rewrite both columns + rows on copy
  • $G3 = keep the columns fixed on copy but allow the rows to be rewritten
  • G$3 = allow the columns to be rewritten but keep the rows fixed on copy
  • $G$3 = fix both columns + rows on copy

Here's a picture of it + this is what I did.

  1. Placed '=VLOOKUP(A3, $G3:I$80, 3, 0)' in A10
  2. copied A10 to A10→B12
  3. Placed a ' (U+0027) APOSTROPHE at the beginning of each cell with an entry

image description

If this helps then please tick the answer (✔)
...and/or show you like it with an uptick ()

edit flag offensive delete link more


+1 just what I needed. :)

David gravatar imageDavid ( 2016-12-21 19:14:23 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-10-07 17:21:21 +0100

Seen: 3,735 times

Last updated: Oct 11 '15