Get the column of a cell via it's reference in another sheet

Heron09 

I will address the problem as formula-only, without regard to VBA.

I hope you are using Excel 2021 or a later version, because the LET function (fonction LET) supported since then allows for more understandable formulas.

The COLUMN function (fonction COLONNE) returns the number of the column for your specified cell.

But to work with a range, it is probably easier to work with column letters than column numbers, so you additionally will want to use the ADDRESS function (fonction ADRESSE) to derive an cell reference from that (and use the LEFT and LEN functions (GAUCHE et NBCAR) to extract the column letter(s) from the cell reference).

Then, to build a qualified range reference for second argument to the VLOOKUP, you need to use the INDIRECT function (fonction INDIRECT).

INDIRECT is a volatile function (fonction volatile), so I hope you will not require many occurrences of it.

Note that below I used a "through" column of AZ (52nd column); depending on your data, you may need to use a column reference further to the right; or if you are confident the retrieved data will never extend that far, you may use a column reference to its left.

The third argument to VLOOKUP (RECHERCHEV) is a column number relative to the range specified as the second argument. That might be a static number in your case, but it can also be calculated. (I do not see a reference to column F in your screenshot, so I made up and used named cell colCompany.)

So, putting that all together, this is a formula for doing that lookup:

=LET( TransMarkerColNum, COLUMN('[AAA.xlsm]Data'!colTransaction),
    TransMarkerAddr, ADDRESS(1, TransMarkerColNum, 4),
    TransColLetters, LEFT(TransMarkerAddr, LEN(TransMarkerAddr) - 1),
    ResultMarkerColNum, COLUMN('[AAA.xlsm]Data'!colCompany),
    ResultRelativeCol, ResultMarkerColNum - TransMarkerColNum + 1,
    LookupRange, INDIRECT("'[AAA.xlsm]Data'!$" & TransColLetters & ":$AZ"),
    VLOOKUP($A15, LookupRange, ResultRelativeCol, FALSE)
)

Heron09 

That's too complicated for me, sorry.