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!

1. Using the match function we find the row for 'Jeans'
2. Again using the match function we find the column number for 'June'
3. The use of match-type as 0 both the times in the match function helps us to find an exact match from our unsorted data
4. Next we use the index function which needs a named range 'sales', the row number of 'jeans' and column number of  'June' or the sales month and we get our result for the sales of jeans in the month of June