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.
Hi liverlarson
You need to add Implicit Intersection "@" in your formula
Implicit Intersection shows formula behavior in Excel where many values are shrink to a single value & it happens when a range or array is passed into a formula. This exactly what is happening right now with the current formula you are passing range in the return value.
For more details on Implicit Intersection check the link below
Solution 01
Just add "@" before VLOOKUP it will resolve your query
=@VLOOKUP([@ID],Table2,MATCH(Table1[#Headers],Table2[#Headers],0),FALSE)after entering your formula will look as below
=VLOOKUP([@ID],Table2,@MATCH(@Table1[#Headers],Table2[#Headers],0),FALSE)----------------------------------------------------------
Solution 02
If you don't wish to do that then you need to change the construction of your formula in the Match function where the lookup value in
MATCH(Table1[#Headers],Table2[#Headers],0)
need to be changed to
MATCH(Table1[[#Headers],[SomeImportantThing]],Table2[#Headers],0)
so the formula construction of the formula will look like this as below
=VLOOKUP([@ID],Table2,MATCH(Table1[[#Headers],[SomeImportantThing]],Table2[#Headers],0),FALSE)----------------------------------------------------------
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com
If you find the above solution resolved your query don't forget mark as Official Answer.
- I was sure the new way Excel is handling arrays was at root, but couldn't get the syntax to force it to intersect like it used to do by default. @Table1[#Headers] was exactly the syntax I was looking for.
Thanks for the contribution!
