Structured Table References: Multiple non-contiguous columns?

%3CLINGO-SUB%20id%3D%22lingo-sub-1164516%22%20slang%3D%22en-US%22%3EStructured%20Table%20References%3A%20Multiple%20non-contiguous%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1164516%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20XLOOKUP%20(which%20is%20AWESOME%2C%20btw)%20to%20return%20multiple%20columns%20from%20a%20table.%20(See%20file%20for%20example).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'd%20like%20to%20do%20is%20return%20Sales%20Amount%20and%20Commission%20Amount%2C%20which%20are%20non-contiguous%20columns%20in%20the%20Table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20seems%20like%20something%20that%20*has*%20to%20be%20possible%2C%20but%20I%20can't%20figure%20it%20out.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20tips%3F%20Solutions%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1164516%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1164639%22%20slang%3D%22en-US%22%3ERe%3A%20Structured%20Table%20References%3A%20Multiple%20non-contiguous%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1164639%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F552116%22%20target%3D%22_blank%22%3E%40Unperterbed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20you%20have%20to%20split%20your%20second%20formula%20as%20you%20can't%20have%20two%20return%20arrays.%20Unless%20I'm%20totally%20mistaken.%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1164663%22%20slang%3D%22en-US%22%3ERe%3A%20Structured%20Table%20References%3A%20Multiple%20non-contiguous%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1164663%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BI%20think%20you're%20right%2C%20but%20that's%20what%20I'd%20like%20to%20avoid!%20I%20*love*%20that%20XLOOKUP%20can%20return%20multiple%20columns%2C%20and%20that%20structured%20references%20makes%20it%20so%20easy%20(e.g.%2C%20Table1%5B%20%5BSales%5D%3A%5BCommission%5D%20%5D)%2C%20but%20it%20seems%20like%20an%20unnecessary%20constraint%20that%20the%20columns%20must%20always%20be%20adjacent%20to%20each%20other.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1168053%22%20slang%3D%22en-US%22%3ERe%3A%20Structured%20Table%20References%3A%20Multiple%20non-contiguous%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1168053%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F552116%22%20target%3D%22_blank%22%3E%40Unperterbed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20use%20titles%20for%20the%20result%2C%20that%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20859px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F170643iA92591769F3C5ED2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(XLOOKUP(%24B15%2CTable1%5BSales%20Person%5D%2CTable1)%2C%2CXMATCH(%24C14%3A%24D14%2CTable1%5B%23Headers%5D%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EJust%20be%20sure%20titles%20here%20are%20exactly%20the%20same%20as%20table%20headers%20(I%20corrected%20by%20copy%2Fpaste)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1168255%22%20slang%3D%22en-US%22%3ERe%3A%20Structured%20Table%20References%3A%20Multiple%20non-contiguous%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1168255%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BWow!%20Definitely%20a%20creative%20approach%3B%20I%20never%20would%20have%20thought%20to%20use%20XLOOKUP%20as%20a%20way%20to%20return%20an%20array%20to%20use%20with%20the%20INDEX%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEven%20though%20that%20accomplishes%20exactly%20what%20I%20was%20looking%20for%2C%20it%20still%20seems%20far%20more%20complex%20than%20it%20should%20be%20(I%20say%20this%2C%20of%20course%2C%20in%20total%20ignorance%20of%20what%20it%20would%20take%20to%20develop%20what%20I'm%20imagining).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suppose%20I'm%20just%20curious%20why%20I%20*can't*%20simply%20give%20a%20list%20of%20header%20names%20in%20the%20structured%20reference%2C%20such%20as%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DXLOOKUP(B15%2CTable1%5BSales%20Person%5D%2CTable1%5B%20%5BSales%20Amount%5D%20%2C%20%5BCommission%20Amount%5D%20%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShould%20this%20be%20a%20feature%20request%3F%3CSPAN%3E%26nbsp%3BIt%20seems%20like%20it'd%20be%20a%20flexible%2C%20powerful%2C%20and%20intuitive%20feature%3C%2FSPAN%3E%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1174900%22%20slang%3D%22en-US%22%3ERe%3A%20Structured%20Table%20References%3A%20Multiple%20non-contiguous%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1174900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F552116%22%20target%3D%22_blank%22%3E%40Unperterbed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20vote%20for%20this%20idea%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F39403732-xlookup-to-return-an-array-of-ranges%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EXLOOKUP%20to%20return%20an%20array%20of%20ranges%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1820707%22%20slang%3D%22en-US%22%3ERe%3A%20Structured%20Table%20References%3A%20Multiple%20non-contiguous%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1820707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BIn%20this%20case%20it%20doesn't%20need%20to%20be%20an%20array%20of%20ranges.%20It%20could%20just%20be%20a%20list%20or%20array%20indicating%20what%20columns%20from%20the%20array%20it%20wants%20returned.%20For%20example%2C%20taking%20something%20like%20%7B1%2C0%2C1%2C1%2C0%2C1%7D%20as%20another%20argument%20would%20specify%20that%20you%20want%20the%201st%2C%203rd%2C%204th%2C%206th%20col%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1824990%22%20slang%3D%22en-US%22%3ERe%3A%20Structured%20Table%20References%3A%20Multiple%20non-contiguous%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1824990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418163%22%20target%3D%22_blank%22%3E%40David_pdx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20didn't%20catch.%20For%20array%20as%26nbsp%3B%3CSPAN%3E%7B1%2C0%2C1%2C1%2C0%2C1%7D%20FILTER%20returns%20columns%20marked%20in%20it%20as%20TRUE.%20In%20previous%20formula%20INDEX%20returns%20columns%20which%20numbers%20returned%20in%20array%20by%20XMATCH.%20But%20XLOOKUP%20doesn't%20return%20array%20of%20ranges.%20Did%20I%20miss%20something%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm using XLOOKUP (which is AWESOME, btw) to return multiple columns from a table. (See file for example).

Annotation 2020-02-10 084655.jpg

 

 

What I'd like to do is return Sales Amount and Commission Amount, which are non-contiguous columns in the Table.

 

This seems like something that *has* to be possible, but I can't figure it out. 

 

Any tips? Solutions? 

 

Thanks!

9 Replies

@Unperterbed 

I believe you have to split your second formula as you can't have two return arrays. Unless I'm totally mistaken. See attached.

 

@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.

@Unperterbed 

If you use titles for the result, that could be

image.png

=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)

@Sergei Baklan 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


@Sergei Baklan In this case it doesn't need to be an array of ranges. It could just be a list or array indicating what columns from the array it wants returned. For example, taking something like {1,0,1,1,0,1} as another argument would specify that you want the 1st, 3rd, 4th, 6th col

@David_pdx 

Sorry, I didn't catch. For array as {1,0,1,1,0,1} FILTER returns columns marked in it as TRUE. In previous formula INDEX returns columns which numbers returned in array by XMATCH. But XLOOKUP doesn't return array of ranges. Did I miss something?

@Unperterbed 

Much as I love XLOOKUP, I think INDEX/XMATCH/XMATCH probably has the edge when it comes to returning two-way lookups as an array.  For example

= LET(
   headers, {"Sales Amount","Commission Amount"},
   fieldIndices, XMATCH(headers, tblSales[#Headers]),
   recordindices, XMATCH(SalesPeople, tblSales[Sales Person]),
   INDEX( tblSales, recordindices, fieldIndices) )

returns the grey-shaded area of

image.png

To sum the amounts and append the total column for each sales person

= LET(
\0, "Collect data",
    recordindices, XMATCH(SalesPeople, tblSales[Sales Person]),
    headers, {"Sales Amount","Commission Amount"},
    fieldIndices, XMATCH(headers, tblSales[#Headers]),
    subTable, INDEX( tblSales, recordindices, fieldIndices),
\1, "Calculate totals for each",
    totalAmounts, MMULT( subTable, {1;1} ),
\2, "Append totals",
    IF( {1,2,3} < 3, subTable, totalAmounts) )

Who was it that said a formula should be no longer than one's thumb?