Forum Discussion
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-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
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
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?
PeterBartholomew1 , thanks
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.
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)
