Forum Discussion
Structured references on tables / with modern excel - need syntax help
So, I have used a formula pattern for years that no longer works with the new modern excel reference framework. Allow me to explain:
Two tables. There is a column on the first table that VLOOKUPs from the second table. Assume that Column containing lookup value is called ID, and that I don't really know which column the value that I want is in on Table2, but the headers match between the tables. So for example, VLOOKUP is in a column on table1 called SomeImportantThing, and I want to look up from table2, but I don't know what column SomeImportantThing column is on that table.
The syntax I use for this is:
VLOOKUP([@ID],Table2,MATCH(Table1[#Headers],Table2[#Headers],0),FALSE)
The way this used to behave was to get the INTERSECT for call to Table1[#Headers], thus passing the value of the name in the header of the current column, to the MATCH, finding that column in the other table, and returning it's position to VLOOKUP.
This no longer works because Table1[#Headers] returns an ARRAY by default, and causes the new #SPILL error. How can I get the value of the header for the CURRENT COLUMN OF TABLE without specifying it by name? The reason I don't want to use MATCH(Table1[#Headers][SomeImportantThing],Table2[#Headers],0) is because I use the exact same fomula copied across many columns, and I don't want every formula to have to be customized. The same formula works for every column, and simply retrieves the value for the matching column in Table2 with a single formula.
Hopefully this kind of makes sense. I've been doing this exact formula for years, but now that I've got all the new features of O365, this specific syntax BROKE a lot.
Now, for the record, I know I could just do something like offset + column() or other combos to navigate to the correct value in header - that's not what I'm asking - I want to know if there is a new structured sytax that allows me to retrieve "current header" value - or some other way to treat this syntax the way old excel used to.
in other words, looking for elegant - not hacky.
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.
10 Replies
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!
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 …
- @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).
Replace Table1[#Headers] with the cell reference of the header (column relative, row absolute). For instance B$2.
=VLOOKUP([@ID],Table2,MATCH(B$2,Table2[#Headers],0),FALSE)Now you can copy/paste it across. Note that this behaves different from dragging, when you apply it to structured table references. If you want to be able to drag it across, you need to fix the reference to the ID as well (e.g. $A3). Up to you to judge if this is elegant or not.
- lol, That is old-school. Yes, this had occurred to me, but defeats the purpose of structured references. B$2 does not have any implicit meaning, where [#Headers] tells me exactly what the context is without having to look at the table or data or know where it is on the canvas.
Thanks for the contributions!
