Ask Your Question

How to stop array reference changing when copying VLOOKUP formula? [closed]

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

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-26 15:49:58.764067

1 Answer

Sort by » oldest newest most voted

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

Alex Kemp gravatar image

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

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 +0200 )edit

Question Tools

1 follower


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

Seen: 7,084 times

Last updated: Oct 11 '15