Forum Discussion

liverlarson's avatar
liverlarson
Brass Contributor
Mar 01, 2020
Solved

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...
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Mar 01, 2020

    liverlarson 

    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.

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 01, 2020

liverlarson 

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.

liverlarson's avatar
liverlarson
Brass Contributor
Mar 01, 2020
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!

Resources