Jul 27 2020 02:50 PM
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
Jul 27 2020 03:41 PM
Perhaps
with
=IFERROR(FILTER($B$6:$K$7,IFNA(XMATCH(A6:A7,A1#),)),"nothing")
if sorting order is not important
Jul 27 2020 03:43 PM - edited Jul 27 2020 03:54 PM
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.
Aug 03 2020 08:30 AM
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
Aug 03 2020 11:26 AM
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.