Using Match Function for Data Analysis in Excel

Data Analysis and MATCH Function

Syntax: Match(lookup value, lookup range, [match type])

Lookup value is the value that you wish to match from the lookup range. Example, ‘Jeans’.
Lookup range is the column or row data range from whcih you wish to pick up a match. Example, a row containing the names of the months or a column consisting of several names of products.
Match type=1: Your lookup range must be sorted in ascending order using the ‘sort’ function. The Match function then displays the location in the lookup range which has the largest value in the range that is less than or equal to the lookup value.
Match type=-1: Here the lookup range needs to be sorted in descending order. The ‘MATCH’ function then returns the row location in the lookup range that contains last value in the range that is greater than or equal to the lookup value.
Match type=0: Here the function looks for the first exact match to the lookup value. If no match exists and match type=0, then #N/A is returned. When the data in the Excel sheet is not sorted we should use this match type which also represents the real world data. However, if we do not specify the match type in our formula, it is assumed that you wish to use match=1!

Further reading:
MATCH function

Leave a Reply

Your email address will not be published. Required fields are marked *