I saw this question and its accepted answer, but I can't wrap my head around how/if it might be used for my situation.
I'm pulling data dynamically from several APIs so the data may be different at any moment, including the actual placement of the data on my sheet. I want to define various cells within the returned data, as named ranges. But without knowing for certain exactly what cell each piece of data will reside in at all times, named ranges don't work well for this. Ideally I could place say a vlookup in a named range, but you can't use formulas at all in named ranges, in Google Sheets.
I don't update the formulas that often, but when I do my calculations break if the named ranges aren't correct anymore. I would have to use vlookup in dozens of formulas on multiple sheets and it just makes the formulas huge and ungainly, when a named range keeps them incredibly tidy and easy to understand. GAS is fine with me as long as I know what to do with it.
Is there any way to get to the same effective result using a named range? I reference these named ranges in many places throughout my workbook, which is why I wanted to use named ranges in the first place... it makes all those other formulas much easier to read & debug.
EDIT: I hope I can provide a bit more of an example here. A hypothetical set of data might be:
But the next time I pull the data, it could look like this:
So now my original 3 values are still there, but they have moved in their rows. A vlookup would make this very easy since it will always adapt to wherever the target value is located, and return the adjacent value. But I need each one of those adjacent values to be its own named range, that will always be correct regardless of where the data moves to.
Also there are often multiples of the key value in the data, and a given formula elsewhere in the sheet needs to pull the first one, or second, or third, etc.