Microsoft
I had a play with your discussion problem. As you specified, I used a table for the source data. I also used a table for the lookup values but I placed the formula in a cell near the criteria table.
= XLOOKUP( @Criteria[Item], Source[Item], Source[[Amount1]:[Amount2]] )
spilt horizontally as required but, being external to any table, the formula does not fill down automatically.
I then tried to use the Item column from the criteria table in its entirety as an array
= XLOOKUP( Criteria[Item], Source[Item], Source[[Amount1]:[Amount2]] )
The result array then spills the full length of the column but truncates to a single column. Since I felt that it is more important that the array behaviour down the column is dynamic, I settled for that and added a further column
= XLOOKUP( Criteria[Item], Source[Item], Source[Amount2] )
to complete the result range.
The final strategy was to revert to INDEX/MATCH and try the formula
= INDEX( Source[[Amount1]:[Amount2]], XMATCH(Criteria[Item], Source[Item]), SEQUENCE(1,2) )
That required only the one formula to spill over both rows and columns, which was my objective, though not the formula I had originally planned to use.
