Use Column() in vlookup as indicators what range columns to display.

[morphingstar]

I am trying to use Column() in vlookup as indicators what range columns to display.
The idea is to make all column related operation independent of how many column are inserted or removed on the left side of a sheet (A). _
Unfortunately when I remove 2 empty columns on left the displayed vlookup data moves 2 columns to the right.
The shift of 2 columns also happens when I insert 2 columns on left side. _
I tried to assist with referring to cell A1, as a zero point position. After column move #REF! Appears. _
The reason I am looking for a solution is as follows: _
I have a table with about 50 columns and 200 rows. Columns are manually ordered according to A-Z in a specific row. _
During use I need to insert 2 columns at an alphabetically suitable place. Or I need to remove 2. _
I have a second table which displays a selected (vlookup) column of the large table, controlled by validity cell. 6 choices controlled by 6 validities, in any column order. _
To conserve space I want to combine the content of 2 adjacent cell of the large table into 1 cell in the small table.
The formula for this looks like this: _
CONCATENATE("$ “,VLOOKUP($A$82,$A$77:$H$94,COLUMN(E82),0),” q ",VLOOKUP($A$82,$A$77:$H$94,COLUMN(F82),0)) _
This setup produces shifts when columns are added or remove left of the table, not part of the lookup range. _
I have a habit of selecting C as first used column once a design has reached usefulness; and Row 100 as table header. _
I am looking for terminology, but let me try this: Using a relative Column system, where column($A$1) in any cell displays “1”, does not become bigger or #REF! when columns are added or removed. _
Fixed vlookup could be used, referring to a data range, like 3,4,5,6,7 in cells above for the last but 1 formula parameter. These parameters loose value when columns are added / removed at A:B. I am trying to replace this method. _
A similar question could be created referring to rows hlookup. _

No macro, please; only spreadsheet functions. Thanks.

---

Added 20210113: I shall check the INDIRECT function. Yes I know about =column(), that is why I try to make use of it.

FILE ADDED as requested 20210113LO lookup+relative column20210113.ods

[dscheikey]

Hello morphingstar, unfortunately your description of your problem is not so easy for me to understand. I would like to help you, but unfortunately it is not possible. Perhaps it is possible for you to upload an example document. Please replace your confidential data with randomly selected ones.
Otherwise, I have two tips that might be of interest to you.
Are you aware that you always get the current column displayed with =Column() without parameters in the brackets?
Also, please have a look at the Indirect function. With this you can build formulas that do not change when you delete columns.

[morphingstar]

My initial text I edited once, now I can no longer - why?
Yes I know about “;” in formulae and always wondered why LO uses / changes it to “,”. I got used to separate with “,” typing, LO is going to change it ; anyway. Softwarriors have their ways not necessarily understood by natural mortals.

[erAck]

The , comma can’t be used as function parameter separator in locales where the decimal separator is comma, additionally (English and all other not using comma decimal separator) Excel users are used to the comma parameter separator, so that’s why in some locales semicolon is used while in others it is comma. It could always be semicolon but users complained …

[erAck]

You want an offset(+1) within the A:H column range, so using an expression that delivers an absolute column number of a reference of course does not work if the range is shifted or columns are inserted or deleted. Make that relative to the start of the range, like

VLOOKUP($A$82;$A$77:$H$94;COLUMN(F82)-COLUMN(A82)+1;0)

(Note that we use the ; semicolon function parameter separator for examples because that is accepted in all locales’ separator constellations).

Of course the expression COLUMN(F82)-COLUMN(A82)+1 is best calculated once in another cell instead of copying it a bunch of times, and that cell referenced then.

[dscheikey]

To insert or delete columns both before your table and within the table, the following combination of functions would work. Please test this if it corresponds to your ideas.

="$ "&TEXTJOIN(" q ";1;OFFSET($B$76:$C$76;MATCH($B82;$B76:$B94;0)-1;COLUMNS($B82:F82)-1))

16105751336568895.ods