Excel not returning 2d spill from XLOOKUP

86 Views Asked by At

I think I can do this with words.

I have the following:-

=XLOOKUP($B6,$W6#,$AA6#:$AD6#,"")

B6 = A key
W6# = A dynamic array of keys
AA6#:AD6# = 4 pieces of data related to key.

Works just fine returning a 1 row and 4 column spill range to the current cell.

What I actually want is n rows and 4 column spill range.The list of keys being held at B6#

I expected this to work:-

          
 =XLOOKUP($B6#,$W6#,$AA6#:$AD6#,"")

It doesn't. It only returns the first column, but does return all the rows.

By experimentation with VLOOKUP, INDEX and FILTER they exhibit the same behaviour.

I can produce the required output in other ways, but I thought I should be able to do it as above.

enter image description here

0

There are 0 best solutions below