MicrosoftThis, IMHO, seems to be unexpected behavior PeterBartholomew1
= 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
Using a formula where the criteria is not in a table:
=XLOOKUP($E$5:$E$9,Table1[Item],Table1[[Data1]:[Data2]],0)
This spills down, but only returns data in the Data1 column.
=XLOOKUP($E$5,Table1[Item],Table1[[Data1]:[Data2]],0)
This spills right and returns values in the Data1 and Data2 column.
Replacing my hard-coded criteria in E5 above with the following:
=UNIQUE(Table1[Item])
allows me to change my XLOOKUP function to:
=XLOOKUP(E5#,Table1[Item],Table1[[Data1]:[Data2]],0)
And it spills down, but will not return the 2nd column.
I cannot see the logic in why it will only spill right if you are referencing a single cell, but will spill down when referencing a range or an array.
Is that by design for some reason JoeMcDaid
