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