Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
The MATCH() function may not look like much, but it can pull back the position of a data element: something that is key for dynamic dashboarding
Download this lesson’s related exercise files.Understanding the MATCH() function.xlsx
34.3 KB Understanding the MATCH() function - Completed.xlsx
Understanding the MATCH() function
Working with Excel’s MATCH() function.
When to use
The Match() function is used to look up where a certain piece of data is stored within a data set, and will return the numeric position of that data in the data set. (This function is often used in combination with the INDEX() function).
The MATCH() function syntax
- Excel Syntax: =MATCH(lookup_value,lookup_array,[match_type])
- What it means: =MATCH([what should it look for],[where is the table to look in],[guess if it can’t find a match])
Returning an Exact MATCH()
- This technique is used where you need to know the position of the lookup_value in the lookup_array, and want to know if the lookup_value does not exist
- To return an Exact positional match, the final parameter (match_type) should be set to 0
- If the lookup_value cannot be found, a #N/A result will be returned
Returning an Approximate MATCH()
- This technique is used where there are gaps in the data (you are searching for a lookup_value that may fall between two data points)
- To return an Approximate positional match, the final parameter (match_type) should be set to -1 or 1
- When using -1 as the final parameter, the lookup_array must be sorted in Ascending order
- When using 1 as the final parameter, the lookup_array must be sorted in Descending order
- An approximate match will return a match for the first row that is not in excess of what you’re looking for
Hints & tips
- To provide a friendly message in case an exact match is not found, use =IFERROR(<match>,”Not Found”)
- It is a best practice to always declare the final parameter so you don’t get burned by the default when it is not what you want
Lesson notes are only available for subscribers.