Forum Discussion
Adding spill to an xlookup with an array result only returns first result
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.
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
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.