The Excel MATCH function

What does the index function do in Excel? 

In summary, the MATCH function in Excel is used for 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 or ascending order depending on what match type is used.

Syntax Explained = MATCH (lookup_value, lookup_array, [match type])

It is highly useful for finding a row reference of an item you are searching for. This can also be used to further incorporate into another function. The MATCH function in Excel works very well with other functions such as IF, VLOOKUP, and INDEX, the latter of which we will cover in another article and look at how they work together.

Lookup_value

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 an ascending, descending, or non-sorted order.

Lookup_array

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, it comes out to be a range that you select with your criteria possibly appearing somewhere within.

Match_type

As per Excel, it defines this as “a number 1, 0, or -1 indicating which value to return.”.

If 1 is chosen (Less than)

The function will find the largest value that is less than or equal to lookup_value. Remember, 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. Remember, 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. Remember, Lookup_array must be placed in descending order.

Ok, let’s start with a very basic non-real-world example.

Examples

Basic Index MATCH in Excel

Try replicating the below function. Write our Index MATCH function Excel in cell E4 to check the range B3:B8 for the occurrence of the content found in cell E3 (in this case, it’s the number 6). As we are 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 of using 1 (less than), 0 (exact match), and -1 (greater than) see below. The same example is given below as above; only this time the RANGE is sorted in ascending order 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 given above the RANGE is sorted in ascending order that 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)

In the same data given above but the RANGE is sorted in descending order and the LOOKUP VALUE is still 9, this will return the result of 1. It happens because the first time it encounters a number greater than or equal to 9, which 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 Excel B14, it’s looking for the content of cell B11, (which is Apr-17) in the lookup_array of B3:N3, note that Index MATCH Excel 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 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 Index MATCH function Excel to help us dynamically find the column index number. This gives us a lookup of Customer D and returns the value found in column 5, in this case, it’s the value 79 shown in B13.

A quick change in the above data is, Customer E and Aug-17, which updates the result to 30, we can check the table manually to make sure it’s working ok.

Conclusion

Have a play around with the table. If you are getting #N/A results, it means the lookup_value cannot be found. To scrutinize this, you can use the FORMULAS > EVALUATE FORMULA toolbar option to help find it. The text must match exactly, and note that it is NOT upper and lower case sensitive. Check your lookup_array fields to ensure the lookup_value that actually appears in the list and also that your column or row index reference actually falls within your lookup_array.