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.
- just a second parting thought - XLOOKUP will work for anyone with the latest version of Excel, but if I wrote that and someone not completely up to date on Excel version tried to open the workbook, it would not work. @Table1[#Headers] I'm assuming is backwards compatible, which is always an important consideration in a corporate setting for a while until it's unreasonable to not be up-to-date, which will be several years into the future. (Granted, with the wide adoption of O365 that window shrinks!)
That is true. Since your problem was with spilt ranges, I went for an O365 solution but, if you need to maintain compatibility with older versions of Excel, you cannot use XLOOKUP. The SINGLE (implicit intersection made explicit) operator '@' comes and goes with the version of Excel in play, to maintain backward compatibility.
I belong to the part of the Excel community that sees VLOOKUP as something of a dog that should never have seen the light of day so, failing XLOOKUP, my preference would be IFNA/ INDEX/ MATCH/ MATCH. It's your workbook though …
- lol. Seems kinda harsh.
Cheers.
- @Table1[#Headers] is what I was looking for. I had tried Table1[@Headers] and various alternatives.
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).
