Using a column reference for a vlookup formula

Question

When inserting a table in Excel you can refer to:

• the data range Table[#data]
• header range Table[#header]
• full column range Table[column name] or
• value of current row under certain column Table[@[colum name]]

which makes formulas very easy to read: =COUNTIF(A:A,A1) becomes

=COUNTIF(Suppliers[IBAN],Suppliers[@[IBAN]])

to quickly check for duplicates (IBAN) of the current row value.

I'm now trying to convert =VLOOKUP([@SupplierCode],Suppliers[Code],2,False) into

=VLOOKUP([@SupplierCode],Suppliers[Code],Suppliers[Name],False)

So in other words, a way to get the column index of a column within a tableObject to use it in a formula (i.e. vlookup).

I could probably write a VBA function that does this, but I'm trying to find out if Excel has a way to handle this without VBA code

The VBA solution I do not want (but might settle for in case it is not possible):

Public Function GetColumnIndex(TableName As String, ColumName As String) As Long
    GetColumnIndex = ActiveSheet.ListObjects(TableName).ListColumns(ColumName).Index
End Function

allowing me to write: =VLOOKUP([@SupplierCode],Suppliers,GetColumnIndex("suppliers","Name"),FALSE)

works, as long as the tables are on the same sheet

Answer 1

Now added as answer: =MATCH("Name",Suppliers[#Headers],0) gives the index of the header matching the name so

The only way I can think to do it without VBA is to use the Match() function

=VLOOKUP([@SupplierCode],Suppliers,MATCH("Name",Suppliers[#Headers],0),FALSE)