Forum Discussion

ALMIS_Rob's avatar
ALMIS_Rob
Brass Contributor
Feb 07, 2020
Solved

XMatch / Index

Hi,   My Excel skills are rusty to the point of atrophy and I'm trying to get my head around XMatch which is an entirely new function to me.   I'm endeavouring to build a formula that will:   M...
excel
Formulas and Functions
  • ALMIS_Rob's avatar
    ALMIS_Rob
    Feb 07, 2020

    SergeiBaklan 

     

    I got a bit of an assist from elsewhere, we ended up with this:

     

    =VLOOKUP($B4,'FY20_21 Version 2'!$B$5:$R$46,MATCH($C$1,'FY20_21 Version 2'!$B$4:$V$4,0),FALSE)

    which works perfectly.

    Thanks for your input though.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 07, 2020

ALMIS_Rob 

With this error most probably or XMATCH is not available for your version of Excel or Billing_Month name is not defined.

And are you sure you'd like to use xmatch('Billing Summary'!C1:D1,Billing_Month) which returns an array, not xmatch(Billing_Month,'Billing Summary'!C1:D1)? If the latest you may use MATCH, that will be no difference.

ALMIS_Rob's avatar
ALMIS_Rob
Brass Contributor
Feb 07, 2020

SergeiBaklan 

 

I got a bit of an assist from elsewhere, we ended up with this:

 

=VLOOKUP($B4,'FY20_21 Version 2'!$B$5:$R$46,MATCH($C$1,'FY20_21 Version 2'!$B$4:$V$4,0),FALSE)

which works perfectly.

Thanks for your input though.

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

    ALMIS_Rob 

    I see, thank you for the update. I only bit confused that MATCH($C$1,'FY20_21 Version 2'!$B$4:$V$4,0) could potentially return number of the column from 1 to 21, and you lookup on the range 'FY20_21 Version 2'!$B$5:$R$46 which has only 17 columns.

     

    Anyway, INDEX/MATCH equivalent could be

    =IFNA(INDEX('FY20_21 Version 2'!$B$5:$R$46,
  MATCH($B4,'FY20_21 Version 2'!$B$5:$B$46,0),
  MATCH($C$1,'FY20_21 Version 2'!$B$4:$V$4,0)
  ), "no such")

Resources