Excel Index Match

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.

1. Index/Match instead of VLOOKUP
2. Pitfall: MATCH argument
3. Example 
4. See also
   1. Excel MATCH returns incorrect value - Super User
   2. How to use Excel Index Match (the right way) - YouTube (Leila)
   3. INDEX MATCH Excel Tutorial - YouTube (Kevin)

Index/Match instead of VLOOKUP

It is recommended to used the Index/Match combination instead of an old-school VLOOKUP.

See for example:

• How to use Excel Index Match (the right way) - YouTube (Leila)
• INDEX MATCH Excel Tutorial - YouTube (Kevin)
• Stop using VLOOKUP in Excel. Switch to INDEX MATCH - YouTube

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)

Excel MATCH returns incorrect value - Super User 

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.

	let
	JiraRootUrl = getNamedCellValue("JiraRootUrl"),
	ProjectKey = getNamedCellValue("ProjectKey"),
	Source = Json.Document(Web.Contents(JiraRootUrl & "/rest/api/2/search?jql=project=" & ProjectKey & " AND issuetype = Epic AND labels in ('process_improvement')")),
	issues = Source[issues],
	#"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
	#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", { "fields","key"}, { "Column1.fields","EpicKey"}),
	#"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"customfield_10104"}, {"Epic Name"})
	in
	#"Expanded Column1.fields"

view raw JiraEpics.pq hosted with ❤ by GitHub

See also

Excel MATCH returns incorrect value - Super User

How to use Excel Index Match (the right way) - YouTube (Leila)

INDEX MATCH Excel Tutorial - YouTube (Kevin)