So, I work somewhere where we keep a spreadsheet for serial codes for consoles (mainly for warranty purposes). However the system is a bit inefficient so I'm trying to change it a bit. We use references numbers for items and then the item name goes in the column after. What I'm wanting is to be able to just type in the reference number and for VLOOKUP to automatically return the item name.

Currently I have this:


This works fine but if I drag that formula to the next row it changes from {G2:H61} to {G3:H62}.

Essentially, what I'm asking is is there any way to make it so the lookup value can change but the range in which it looks through doesn't? At the moment the only solution I have is just repeating the table in which the master list of reference numbers is kept. As in, that master list is repeated from G2 down to H610.

This isn't really ideal for when new console variations would launch as I'd have to edit the entire thing again.

Thanks for any help, if need be, I can throw together a separate spreadsheet to share just to demonstrate what I mean.

  • Did you use $ to block the reference of the cells ? A dollar sign ($) can be used before the column and/or row part of a reference to control how the reference will be updated. The dollar sign causes the corresponding part of the reference to remain unchanged. – pjmg Oct 4 '20 at 9:04
  • I have now, thanks. I wasn't aware that the $ symbol could be used in that way. – CyclonusG1 Oct 4 '20 at 22:59

You need to select absolute cell references:

So in your example to prevent G2:H61 moving enter $G$2:$H$61


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.