Forum Discussion
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...
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 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")