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.
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.If you think that is harsh, you ought to hear my rant about the abomination that is direct cell referencing (A1 and R1C1 notations). End-user programming (EUP) is what has made Excel ubiquitous but it also produces cheap and nasty programming in the hands of those that deny they are programming. As Dan Bricklin said "It would be possible to do things the programmers' way, but that would be tedious". There are worse things than a bit of tedium.
I even believe that macro recorder does not produce the best code known to mankind, so I am clearly a lost cause! Each to their own …