Forum Discussion
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:
Match text from 'Sheet1'ColumnB to 'Sheet2'ColumnB then match 'Sheet1'C1 to 'Sheet2'RowH4:S4 and paste the value from the meeting point of the two.
At the moment I've got this:
=INDEX('FY20_21 Version 2'!B5:T44,xmatch('Billing Summary'!B4,'FY20_21 Version 2'!B5:B23),xmatch('Billing Summary'!C1:D1,Billing_Month))
and it's giving me a #NAME? error which I'm struggling to fix/resolve.
Any suggestions/advice would be welcomed.
Regards,
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.
3 Replies
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.
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.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")
