# How to stop array reference changing when copying VLOOKUP formula? [closed] This post is a wiki. Anyone with karma >75 is welcome to improve it.

Libreoffice calc 4.3.7.2 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 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

Sort by » oldest newest most voted 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 