Xlookup isn't working. Is it possible to have multiple return arrays for spilled lookup value?

[Nishkarsh31]

Xlookup doesn't seem to work when the lookup value is a spilled range and

there are multiple return arrays to choose from.

I've attached a sample file. It's self explanatory.

I need one Xlookup dynamic formula for Cell B2 in Receipt sheet

Is it possible?
@Sergei Baklan @Peter Bartholomew 

[Nishkarsh31]

there are multiple return arrays to choose from.

I've attached a sample file. It's self explanatory.

I need one Xlookup dynamic formula for Cell B2 in Receipt sheet

Is it possible?
@Sergei Baklan @Peter Bartholomew 

[#3]

@Nishkarsh31 

I am not convinced over the syntax of your XLOOKUP.  The third parameter needs to be the arrays to return data from, not indices.

= TRANSPOSE(
   XLOOKUP($G$1&$J$1,Masterfile2021[Concat],Masterfile2021[[Apple]:[Orange]])
  )

[excuse the direct cell references, I would normally use 'date&name']

[#4]

@Nishkarsh31 

File

[#5]

@Nishkarsh31 

As variant

=TRANSPOSE(
   IF({1;0},
        Masterfile2021[[#Headers],[Apple]:[Orange]],
        FILTER(Masterfile2021[[Apple]:[Orange]],
               Masterfile2021[Date]&Masterfile2021[Name]=$G$1&$J$1
        )
))

We don't need Concat column at all.

[#6 · Accepted solution]

@Nishkarsh31 

A further thought.  If you wish to look up the fruit rather than simply returning data from a contiguous range, you could use a nested XLOOKUP

= LET(
  record,  XLOOKUP(Date&Name, Masterfile2021[Concat], Masterfile2021),
  results, XLOOKUP(Products,  Masterfile2021[#Headers], record),
  results )

If you need to return a 2D array, that has to be INDEX/XMATCH/XMATCH or else you enter the murky world of recursive LAMBDA functions.