Forum Discussion

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

excel

Formulas and Functions

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

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.