Forum Discussion

Unperterbed's avatar
Unperterbed
Copper Contributor
Feb 10, 2020

Structured Table References: Multiple non-contiguous columns?

I'm using XLOOKUP (which is AWESOME, btw) to return multiple columns from a table. (See file for example).     What I'd like to do is return Sales Amount and Commission Amount, which are non...
excel
Formulas and Functions
Unperterbed's avatar
Unperterbed
Copper Contributor
Feb 10, 2020

Riny_van_Eekelen I think you're right, but that's what I'd like to avoid! I *love* that XLOOKUP can return multiple columns, and that structured references makes it so easy (e.g., Table1[ [Sales]:[Commission] ]), but it seems like an unnecessary constraint that the columns must always be adjacent to each other.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 11, 2020

    Unperterbed 

    If you use titles for the result, that could be

    =INDEX(XLOOKUP($B15,Table1[Sales Person],Table1),,XMATCH($C14:$D14,Table1[#Headers],0))

    Just be sure titles here are exactly the same as table headers (I corrected by copy/paste)

    • Unperterbed's avatar
      Unperterbed
      Copper Contributor
      Feb 12, 2020

      SergeiBaklan Wow! Definitely a creative approach; I never would have thought to use XLOOKUP as a way to return an array to use with the INDEX function.

       

      Even though that accomplishes exactly what I was looking for, it still seems far more complex than it should be (I say this, of course, in total ignorance of what it would take to develop what I'm imagining).

       

      I suppose I'm just curious why I *can't* simply give a list of header names in the structured reference, such as:

       

       

      =XLOOKUP(B15,Table1[Sales Person],Table1[ [Sales Amount] , [Commission Amount] ])

       

       

      Should this be a feature request? It seems like it'd be a flexible, powerful, and intuitive feature

Resources