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

[Nishkarsh31]

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 there a fix to it?

[#2 · Accepted solution]

@Nishkarsh31 

As variant

=INDEX(Enrolment,XMATCH(A2#,Enrolment[Name]),{2,3,4})

[#3]

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

[#4]

@Peter Bartholomew , 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.

[#5]

Thank you sir@Sergei Baklan 

I need to know why we used Xmatch over match, I've gotten the same results with Match.

Also to make the columns dynamic, I've used this.
Is there a better solution?

=INDEX(Enrolment,MATCH(A2#,Enrolment[Name]),SEQUENCE(1,COLUMNS(Enrolment)-2,2)

[#6]

@Nishkarsh31 

What is better depends on with which you are more familiar.

MATCH() by default performs approximate search which is not reliable, that's the main difference. XMATCH() has more options. In your case not a big difference.

SEQUENCE/COLUMNS - good enough.