SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2204647%22%20slang%3D%22en-US%22%3EXlookup%20isn't%20working.%20Is%20it%20possible%20to%20have%20multiple%20return%20arrays%20for%20spilled%20lookup%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2204647%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-subject-wrapper%20lia-component-subject%20lia-component-message-view-widget-subject-with-options%22%3E%3CSPAN%3EXlookup%20doesn't%20seem%20to%20work%20when%20the%20lookup%20value%20is%20a%20spilled%20range%20and%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3Ethere%20are%20multiple%20return%20arrays%20to%20choose%20from.%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20attached%20a%20sample%20file.%20It's%20self%20explanatory.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20need%20one%20Xlookup%20dynamic%20formula%20for%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ECell%20B2%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ein%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EReceipt%20sheet%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIs%20it%20possible%3F%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2204647%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2204862%22%20slang%3D%22en-US%22%3ERe%3A%20Xlookup%20isn't%20working.%20Is%20it%20possible%20to%20have%20multiple%20return%20arrays%20for%20spilled%20lookup%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2204862%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20convinced%20over%20the%20syntax%20of%20your%20XLOOKUP.%26nbsp%3B%20The%20third%20parameter%20needs%20to%20be%20the%20arrays%20to%20return%20data%20from%2C%20not%20indices.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20TRANSPOSE(%0A%20%20%20XLOOKUP(%24G%241%26amp%3B%24J%241%2CMasterfile2021%5BConcat%5D%2CMasterfile2021%5B%5BApple%5D%3A%5BOrange%5D%5D)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%5B%3CEM%3Eexcuse%20the%20direct%20cell%20references%2C%20I%20would%20normally%20use%3C%2FEM%3E%20'date%26amp%3Bname'%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2204873%22%20slang%3D%22en-US%22%3ERe%3A%20Xlookup%20isn't%20working.%20Is%20it%20possible%20to%20have%20multiple%20return%20arrays%20for%20spilled%20lookup%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2204873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFile%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor
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 

4 Replies

@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']

@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.

best response confirmed by Nishkarsh31 (Contributor)
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 )

image.png

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.