Google Sheets: VLOOKUP: Named Ranges - Insert/Move Columns

1
votes

I'm doing performance calculations on an America's Cup AC75 yacht, and have found Named Ranges very helpful in equations I use.

On a 'Data' sheet, I now have a lot of information that I'd like to reference as a Named Range, and use via VLOOKUP(), in my 'Analysis' sheet.

A problem I have struck is that if I need to Insert/Move a column in this new Named Range, the VLOOKUP function does not "update" the column that it should return.

For example, if a cell value is "=VLOOKUP(B6, Sail_Vectors, 10, false)", but I have to insert a new column before col 10 in the Named Range "Sail_Vectors", the desired column becomes col 11 - but the lookup still returns col 10, which is now incorrect.

I may be asking too much, but just wondering if anyone might have a work-around, or something I've overlooked?

1
google-sheetsvlookupnamed-ranges
Can you provide a sample spreadsheet (free of sensitive information) in order to clarify your question? - Iamblichus
It's very easy to recreate yourself. In anew ss, add a second sheet, "B". In this sheet, add a few key values in A1:A3 , 1,2,3. Then add some random values in B1:D3. Now create a Named Range for A1:D3, eg "test". - maxhugen
It's very easy to recreate yourself. In a new ss, add a second sheet, "B". In this sheet, add a few key values in A1:A3 , 1,2,3. Then add some random values in B1:D3. Now create a Named Range for A1:D3, eg "test". In the first sheet, make A1= 2, in B2 enter "=VLOOKUP(A1, test, 3, false)". It will return whatever value you entered in "B"!C2. Now insert a new col between cols B & C in sheet B, and see what happens to the lookup in sheet "A". - maxhugen

1 Answers

2
votes

If your sheet Data has headers, you can use them in order to reference your desired column dynamically, using MATCH:

=VLOOKUP(A1,Sail_Vectors,MATCH("Header C",Data!1:1),false)
  • Sheet Analysis:

enter image description here

  • Sheet Data:

enter image description here