Feb 06 2021 11:07 AM
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?
Feb 06 2021 01:34 PM
SolutionFeb 06 2021 02:13 PM
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.
Feb 06 2021 02:36 PM
@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.
Feb 06 2021 11:08 PM
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)
Feb 07 2021 08:29 AM
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.
Feb 06 2021 01:34 PM
SolutionAs variant
=INDEX(Enrolment,XMATCH(A2#,Enrolment[Name]),{2,3,4})