I have stumbled recently again upon the Index/Match combo and forgot that the 3rd argument in MATCH is most needed and shall be set to 0.
See the formula highlighted in the current cell value.
Index/Match instead of VLOOKUP
It is recommended to used the Index/Match combination instead of an old-school VLOOKUP.
See for example:
Pitfall: MATCH argument
I was surprised by the wrong result returned by the MATCH function if you don't use the right match_type argument. (The default isn't the value you would expect)
So, remember to use the third argument MATCH(lookup, range, 0)
Example
I have recently used the Index/Match to expand in a Table of Jira issues the Epic Name.
When you export an issue in Jira you normally only get the Epic Issue Key in the Epic Link field.
I have exported in Excel PowerQuery in a separated table the list of Epics in my Projects with their keys and name and then with a Index/Match formula in my Issue Table I can nicely expand the epics with their real name and add slicer with explicit names.
See below how great it looks like! ;-)
See the formula highlighted in the current cell value.
=INDEX(Table_jira_epics[Epic Name],MATCH([@[Epic Link]],Table_jira_epics[EpicKey],0))
Without the 0 third argument the MATCH function was returning for TPI-1019 -> the value TM same as for TPI-1017. It was very unexpected, least to say.
No comments:
Post a Comment