How do I create a named range which refers to cell value, not cell location? Or, how do I sort but preserve the value of named range?

I have a Google Sheet which has several named cells. When I sort the sheet, other cells which refer to the named range(s) change value because the name refers to the cell by location, not value.

For example:


A named range, bananaCount refers to B2.

A5 is a formula which uses the named range: =bananaCount.

If I sort the sheet by column A (fruit names) the value of B2 and the the bananaCount named range will become 1 because row 3 (Apple, ...) becomes row 2, which causes A5 changes to 1. I want A5 to remain 5 (B3 after sorting).

Now, if I cut/paste B2, the named range reference does update correctly. However, sorting does not update the reference.

(What I really want is a variable to refer to the cell value, regardless of moving the cell value elsewhere in the sheet by any mechanism -- either sort or copy/cut/paste.)

Any suggestions?

2 Answers 2


You can't really have "variables" in a spreadsheet; it's not a programming language. The values are contained in cells and can be referred to by location, possibly relative to location of other cells. If cells get rearranged so that the relations within rows or columns are lost, that is already a problem.

In your example, you want to refer to Count of Bananas in a way that is independent of sorting. The function vlookup does this nicely:

=vlookup("Banana", A:B, 2, False)

This means: get the value in column B that's in the row with "Banana".

Of course, if you sort column B but not column A, this is going to break; but then the entire logic of Fruit/Count columns is broken.

  • This will work, I can keep the column order stable to preserve a reference to the second column (B2); and you're right, I'll never sort by one column and not another. Thanks for the suggestion to use vlookup. Nov 9, 2015 at 15:54
  • 1
    but here's the thing... if you insert or delete rows, the named range is automatically adjusted, whereas with a sort it is not. that seems like of like a bug or oversight or at the very least an inconsistency which is very annoying in my opinion.
    – Michael
    Feb 4, 2019 at 23:54

Excel handles this kind of sorted or filtered range reference just fine. Its a pretty serious shortcoming of google sheets, apparently.

  • Yup. I like Google products, especially what I can do with Google Apps Script but the more I use it the more shortcomings I see compared to Excel. They make great products but none are really ready for large scale enterprise use where productivity and capabilities are crucial. Oct 2, 2019 at 2:51
  • yeah this bit me in the butt, got used to excel handling named ranges when you do anything that moves them around. I think this is a bug actually. That can't have been the intention to break named ranges.
    – toddmo
    Jan 1, 2020 at 17:44
  • @IMTheNachoMan @toddmo I suggest using Help -> Report a problem in Google Sheets to ask for this feature. AFAIK, that is the best (perhaps only) way to send feedback to Google which will be seen by the team. Apr 4, 2020 at 20:19

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.