Forum Discussion
Structured references on tables / with modern excel - need syntax help
The direct reply to the question as posed is to reinstate the implicit intersection.
Table1[#Headers] is a single-row range so the dynamic array operator '@' will reduce the array to a single column header, giving a [partial] formula
MATCH(@Table1[#Headers], Table2[#Headers], 0)
The more general answer to the use case described is to move away from VLOOKUP as soon as is practical and certainly never use it in new functionality. XLOOKUP will do a better job and is far more transparent.
= XLOOKUP( [@ID], Table2[ID], Table2[Value] )
offers the simplest syntax but, if you need to lookup the current header, a nested XLOOKUP will return the entire column from Table2 to be used as the return column
= XLOOKUP( [@ID], Table2[ID], XLOOKUP(@Table1[#Headers],Table2[#Headers],Table2) )
Note: It is also possible to lookup a record with one XLOOKUP and the field with the other and intersect the ranges to return a single cell reference containing the result
= XLOOKUP([@ID], Table2[ID], Table2) XLOOKUP(@Table1[#Headers],Table2[#Headers],Table2)
but that is probably a step too far for little benefit.
The direct reply to the question as posed is to reinstate the implicit intersection.
Table1[#Headers] is a single-row range so the dynamic array operator '@' will reduce the array to a single column header, giving a [partial] formula
MATCH(@Table1[#Headers], Table2[#Headers], 0)
The more general answer to the use case described is to move away from VLOOKUP as soon as is practical and certainly never use it in new functionality. XLOOKUP will do a better job and is far more transparent.
= XLOOKUP( [@ID], Table2[ID], Table2[Value] )
offers the simplest syntax but, if you need to lookup the current header, a nested XLOOKUP will return the entire column from Table2 to be used as the return column
= XLOOKUP( [@ID], Table2[ID], XLOOKUP(@Table1[#Headers],Table2[#Headers],Table2) )
Note: It is also possible to lookup a record with one XLOOKUP and the field with the other and intersect the ranges to return a single cell reference containing the result
= XLOOKUP([@ID], Table2[ID], Table2) XLOOKUP(@Table1[#Headers],Table2[#Headers],Table2)
but that is probably a step too far for little benefit.
I've not been up to speed on latest features. I did hear about XLOOKUP and the new array functionality which is why I even knew what the spill error was about, but haven't really used latest version of Excel for more than a day yet. Looks like it's good syntax that hearkens back to the pre-2003 LOOKUP function, but with better features (and actually works).