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

[JonnyGreenwood]

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

[#2]

@JonnyGreenwood 

Perhaps

with

=IFERROR(FILTER($B$6:$K$7,IFNA(XMATCH(A6:A7,A1#),)),"nothing")

if sorting order is not important

[#3]

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

[#4]

@Peter Bartholomew 

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

[#5]

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