Function VLOOKUP from another workbook throws 'Cell currently evaluated contains a constant'

I try to do a simple VLOOKUP from another workbook (in the same folder, open), for which the syntax is:

 =VLOOKUP([@MtkNr];'[file.xlsx]Gesamtliste'!$E$2:$Z$76;5;0)

(Ideally I would write

 =VLOOKUP([@MtkNr];'[file.xlsx]Gesamtliste'!Teilnehmer[MtkNr];[Note];0)

But as I understood table names are not allowed if it is from another workbook.)

But Excel simply shows me the formula, and if I try to evaluate it, it tells me

 The cell currently evaluated contains a constant

What can be the problem here? Thanks in advance!

1 Answer

[Note] is not an integer; it is the column header label for one of the columns in your table. To convert it to a number, use MATCH across the header row.

=VLOOKUP([@MtkNr]; '[file.xlsx]Gesamtliste'!Teilnehmer[MtkNr]; MATCH("Note", MtkNr[#Headers], 0); FALSE)

You might be happier with an INDEX/MATCH function pair using structured table references.