1

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?

maxhugen
  • 1,870
  • 4
  • 22
  • 44
  • Can you provide a sample spreadsheet (free of sensitive information) in order to clarify your question? – Iamblichus Dec 02 '20 at 08:52
  • 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 Dec 02 '20 at 11:01
  • 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 Dec 02 '20 at 11:08

1 Answers1

3

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

Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • Thanks, that sounds like a workable solution. At first, I was a bit concerned because I sometimes change the headers (usually to improve clarity), but I realised that I could add an extra row with "pseudo" headers that would remain unchanged and use that. I had to read the docs on MATCH(), should it not be MATCH("Header C", Data!B1:F1) ? – maxhugen Dec 02 '20 at 13:13
  • 1
    @maxhugen `Data!B1:F1` it could be that range too, but if you're gonna have a variable number of columns in your range, I'd suggest just grabbing all the headers row. – Iamblichus Dec 02 '20 at 13:15
  • 1
    Ah, I see, I didn't know that's what "Data!1:1" does, still learning! Excellent, already implementing it, with 100+ cols it also makes it heaps easier to see what I'm actually looking up! I also made the "lookup headers" a named range. Thank you for a good solution. :) – maxhugen Dec 02 '20 at 13:42