How to use MATCH function in Excel - formula examples

Excel MATCH function - syntax and uses

The MATCH function in Excel searches for a specified value in a range of cells, and returns the relative position of that value.

The syntax for the MATCH function is as follows:

Lookup_value (required) - the value you want to find. It can be a numeric, text or logical value as well as a cell reference.

Lookup_array (required) - the range of cells to search in.

Match_type (optional) - defines the match type. It can be one of these values: 1, 0, -1. The match_type argument set to 0 returns only the exact match, while the other two types allow for approximate match.

• 1 or omitted (default) - approximate match (closest smaller). Finds the largest value in the lookup array that is less than or equal to the lookup value. Requires sorting the lookup array in ascending order, from smallest to largest or from A to Z.
• 0 - exact match. Finds the first value in the array that is exactly equal to the lookup value. No sorting is required.
• -1 - approximate match (closest larger). Finds the smallest value that is greater than or equal to the lookup value. The lookup array should be sorted in descending order, from largest to smallest or from Z to A.

To better understand the MATCH function, let's make a simple formula based on this data: students names in column A and their exam scores in column B, sorted from largest to smallest. To find out where a specific student (say, Laura) stands among others, use this simple formula:

=MATCH("Laura", A2:A8, 0)

Optionally, you can put the lookup value in some cell (E1 in this example) and reference that cell in your Excel Match formula:

=MATCH(E1, A2:A8, 0)

As you see in the screenshot above, the student names are entered in an arbitrary order, and therefore we set the match_type argument to 0 (exact match), because only this match type does not require sorting values in the lookup array. Technically, the Match formula returns the relative position of Laura in the range. But because the scores are sorted from largest to smallest, it also tells us that Laura has the 5^th best score among all students.

How to use MATCH in Excel - formula examples

Now that you know the basic uses of the Excel MATCH function, let's discuss a few more formula examples that go beyond the basics.

Excel VLOOKUP and MATCH

This example assumes you already have some basic knowledge of Excel VLOOKUP function. And if you do, chances are that you've run into its numerous limitations (the detailed overview of which can be found in Why Excel VLOOKUP is not working) and are looking for a more robust alternative.

One of the most annoying drawbacks of VLOOKUP is that it stops working after inserting or deleting a column within a lookup table. This happens because VLOOKUP pulls a matching value based on the number of the return column that you specify (index number). Because the index number is "hard-coded" in the formula, Excel is unable to adjust it when a new column(s) is added to or deleted from the table.

The Excel MATCH function deals with a relative position of a lookup value, which makes it a perfect fit for the col_index_num argument of VLOOKUP. In other words, instead of specifying the return column as a static number, you use MATCH to get the current position of that column.

To make things easier to understand, let's use the table with students' exam scores again (similar to the one we used at the beginning of this tutorial), but this time we will be retrieving the real score and not its relative position.

Assuming the lookup value is in cell F1, the table array is $A$1:$C$2 (it's a good practice to lock it using absolute cell references if you plan to copy the formula to other cells), the formula goes as follows:

=VLOOKUP(F1, $A$1:$C$8, 3, FALSE)

The 3^rd argument (col_index_num) is set to 3 because the Math Score that we want to pull is the 3^rd column in the table. As you can see in the screenshot below, this regular Vlookup formula works well:

But only until you insert or delete a column(s):

So, why the #REF! error? Because col_index_num set to 3 tells Excel to get a value from the third column, whereas now there are only 2 columns in the table array.

To prevent such things from happening, you can make your Vlookup formula more dynamic by including the following Match function:

MATCH(E2,A1:C1,0)

Where:

• E2 is the lookup value, which is exactly equal to the name of the return column, i.e. the column from which you want to pull a value (Math Score in this example).
• A1:C1 is the lookup array containing the table headers.

And now, include this Match function in the col_index_num argument of your Vlookup formula, like this:

=VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE)

And make sure it works impeccably no matter how many columns you add or delete:

In the screenshot above, I locked all cell references for the formula to work correctly even if my users move it to another place in the worksheet. A you can see in the screenshot below, the formula works just fine after deleting a column; furthermore Excel is smart enough to properly adjust absolute references in this case:

Excel HLOOKUP and MATCH

In a similar manner, you can use the Excel MATCH function to improve your HLOOKUP formulas. The general principle is essentially the same as in case of Vlookup: you use the Match function to get the relative position of the return column, and supply that number to the row_index_num argument of your Hlookup formula.

Supposing the lookup value is in cell B5, table array is B1:H3, the name of the return row (lookup value for MATCH) is in cell A6 and row headers are A1:A3, the complete formula is as follows:

=HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE)

As you have just seen, the combination of Hlookup/Vlookup & Match is certainly an improvement over regular Hlookup and Vlookup formulas. However, the MATCH function doesn't eliminate all their limitations. In particular, a Vlookup Match formula still cannot look at its left, and Hlookup Match fails to search in any row other than the topmost one.

To overcome the above (and a few other) limitations, consider using a combination of INDEX MATCH, which provides a really powerful and versatile way to do lookup in Excel, superior to Vlookup and Hlookup in many respects. The detailed guidance and formula examples can be found in INDEX & MATCH in Excel - a better alternative to VLOOKUP.

Practice workbook for download

Excel MATCH formula examples (.xlsx file)