Forum Discussion

JonnyGreenwood's avatar
JonnyGreenwood
Copper Contributor
Jul 27, 2020

Adding spill to an xlookup with an array result only returns first result

Hi all,

 

I have a situation where I need to lookup a range in a table on a spill reference, and that range needs to be dynamic. 

 

I can take the below formula to get my range to do my lookup against:

 

=XLOOKUP(A1,$A$6:$A$7,$B$6:$K$7)

 

And by itself this spills to the right and pulls back the correct range to do my lookup against, which I can nest within another xlookup. As soon as I change the reference to spill:

 

=XLOOKUP(A1#,$A$6:$A$7,$B$6:$K$7)

 

I can see the result I want by hitting F9 but when I let the calculation play out it results in the first value of the array only. Is there a workaround so that I can get this formula to spill vertically along with A1 whilst returning the array? Otherwise I'm thinking I'll likely have to preemptively drag this formula down? I'm limited to working in 32 bit excel and will most likely be about a year of updates behind

 

 

Kind regards,

Jonny

excel
Formulas and Functions

4 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 27, 2020

    JonnyGreenwood 

    Sorry but that is the way it is,  Excel cannot handle arrays of arrays.

    = XLOOKUP(@spillRange, lookupArray, returnTable)

    looks up a single value and returns a row from the table (assuming XLOOKUP is working in HLOOKUP mode as in your case).

    = XLOOKUP( spillRange, lookupArray, returnTable)

    will lookup multiple values but only from the first column.  You would need to specify columns from your table individually, either constructing the relative references or by using INDEX.

    If you require a 2D spill then INDEX/XMATCH will do the job

    = INDEX( returnTable, XMATCH(spillRange, lookupArray), SEQUENCE(1,10) )

    This last is a special case of the INDEX/XMATCH/XMATCH that you might use for a 2D lookup.

    • Vie29000's avatar
      Vie29000
      Copper Contributor
      Aug 03, 2020

      PeterBartholomew1 

      In my table, I also want to populate its column and rows automatically using dynamic array function. (Screenshots attached).
      I also experienced the same XLOOKUP 2D issue. I then tried the formula mentioned in your previous post with cats and dogs examples... but I probably did not parameter it properly: no data was retrieve in my Database table

      In my case, for each column of my Database table, I refer to a specific data extraction tab, and All the extraction tabs are formated with 2 columns: doc name & Specific data.

      Thanks


      Thanks

       

       

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Aug 03, 2020

        Vie29000 

        It looks to me if the parameters are in a tangle.  Start off with just the match,

        = XMATCH( Doc_Name, tblTag[Doc Name])

        If that doesn't return a plausible set of record numbers, the INDEX is not going to give anything useful.

         

        Then nest the XMATCH within the INDEX.  the first parameter can be the entire lookup table, next the match to give the row number and, finally, an array of column indices

        = INDEX( tblTag, XMATCH( Doc_Name, tblTag[Doc Name]), {2,3} )

        For two columns, it is not worth bothering with the SEQUENCE function, an array constant {2,3} will do the job more concisely. 

Resources