The Excel MATCH function
What does it do?
In brief summary, the Excel MATCH function looks for a given criteria in a selected range (vertically or horizontally) and returns the row or column number (within that range) where it appears. The range can be sorted in descending, any or ascending order depending on what match type is used.
=MATCH(lookup_value, lookup_array, [match type])
Useful for finding a row reference of an item you are searching for, this can then be used to further incorporate into another function. The MATCH function works very well with other function such as IF, VLOOKUP and INDEX, that latter of which we’ll cover in another article and look at how they work together.
As per Excel, it defines this part of the syntax as “the value you use to find the value you want in the array, a number, text, or any logical value, or a reference to one of these.”. This is where you instruct the function to find criteria you define, it must appear in the array and either be in in an ascending, descending or non-sorted order.
As per Excel, it defines this as “a contiguous range of cells containing possible lookup values, an array of values, or a reference to an array.”. Basically, this is the range you select with your criteria possibly appearing somewhere within.
As per Excel, it defines this as “a number 1, 0, or -1 indicating which value to return.”.
If a 1 is chosen (Less than)
The function will find the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order.
If a 0 is chosen (Exact match)
The function will find the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
If a -1 is chosen (Greater than)
The function will find the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order.
Ok, let’s start with a very basic non-real-world example.
Try replicating the below. Write our MATCH function in cell E4 to check the range B3:B8 for the occurrence of content found in cell E3 (in this case it’s the number 6). As we using 0 for EXACT MATCH, our set of data in B3:B8 is not in any order.
In the function below, E3 is our lookup_value, with B3:B8 being our lookup_array, and finally the match_type of 0 instructs the function to find an EXACT match.
The result will yield 3, this indicates that the lookup_value of 6 appears on row 3 of the lookup_array B3:B8. Remember this is not row 3 of the worksheet, but row 3 of the range we have selected.
To demonstrate the difference in using 1 (less than), 0 (exact match), and -1 (greater than) see below. Same example below as above only this time the RANGE is sorted ascending for 1 and descending for -1. The function will return #N/A when there is a mismatch of range order vs match_type selected.
Match type 1 (less than)
In the example above the RANGE is sorted in ascending order, contains 2 x 8’s and the LOOKUP VALUE is 9, this will return the result of 5. This is because the first time it encounters a number less than or equal to 9 is on row 5 (working from the bottom of the range up).
Match type -1 (greater than)
Same data above but RANGE is sorted in descending order and the LOOKUP VALUE is still 9, this will return the result of 1. This is because the first time it encounters a number greater than or equal to a 9 is on row 1 (working from the top of the range down).
So that’s the basics out the way, let’s look at some other data and examples.
Sales Data Example
Ok, so using the same set of data from the HLOOKUP article, and just changing Client to Customer.
Above we have a MATCH function in B14, its looking for the content of cell B11, (which is Apr-17) in the lookup_array of B3:N3, note that MATCH works both vertically and horizontally. In this case it’s telling us Apr-17 appears in column 5 of our lookup_array.
Looking at the VLOOKUP, we’ve asked it to lookup B10 (which is Customer D), in the range B3:N8, and we know from VLOOKUPS that our lookup criteria MUST be in the first column of our range. Next you would normally type in a col_index_num, but this time we’re using the MATCH function to help us dynamically find the column index number. This gives us a lookup of Customer D and return the value found in column 5 in this case it’s the value 79 shown in B13.
A quick change above to Customer E and Aug-17, updates the result to 30, we can check the table manually to make sure it’s working ok.
Have a play around with the table. If you are getting #N/A results, it means either the lookup_value cannot be found, to scrutinise this you can use the FORMULAS > EVALUATE FORMULA tool bar option to help find it. Text must match exactly, note it is NOT upper and lower case sensitive. Check your lookup_array fields to ensure the lookup_value actually appear in the list and also that your column or row index reference actually fall within your lookup_array.