Forum Discussion

Brass Contributor

Feb 06, 2021

Solved

How to use Xlookup with spilled range as lookup value? I want the range to spill horizontally.

I'm attaching the sample file. In "Cal" sheet, I want a single cell formula in A2, to lookup values in subsequent columns For some reason Xlookup is spilling lookup vertically in rows. Is ther...

Xlookup Problem.xlsx13 KB

Formulas and Functions

Macros and VBA

office 365

SergeiBaklan
Feb 06, 2021
Nishkarsh31

As variant

=INDEX(Enrolment,XMATCH(A2#,Enrolment[Name]),{2,3,4})
Xlookup Problem.xlsx13 KB

PeterBartholomew1

Silver Contributor

Feb 06, 2021

Nishkarsh31

With XLOOKUP you can either perform the lookup row by row

= XLOOKUP(@Names#, Enrolment[Name], Enrolment[[A]:[C]])

or you can do it column by column

= XLOOKUP(Names#, Enrolment[Name], Enrolment[A])

but it will not return a 2D array (Excel doesn't like arrays of array - yet)

It is possible to collect the rows or columns together using a recursive LAMBDA function (365 beta) but that would be something of a last resort. In this case INDEX/XMATCH is superior to XLOOKUP.

I did post a UserVoice request for this to be improved but right now, I can't even find UserVoice, never mind my suggestion.

SergeiBaklan
Diamond Contributor
Feb 06, 2021
PeterBartholomew1 , IMHO, last resort is Power Query, but it depends on entire project. I'm not sure recursive LAMDA works in such case on relatively big data source.