Forum Discussion

Nishkarsh31's avatar
Nishkarsh31
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...
excel
Excel for web
Excel on Mac
Excel on mobile
Formulas and Functions
Macros and VBA
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 06, 2021

    Nishkarsh31 

    As variant

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

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 06, 2021

Nishkarsh31 

As variant

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

 

  • Nishkarsh31's avatar
    Nishkarsh31
    Brass Contributor
    Feb 07, 2021

    Thank you sirSergeiBaklan 

    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)

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 07, 2021

      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.

Resources