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
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 28, 2020

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

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?

Resources