Excel MATCH function

The ‘MATCH’ function in Excel will return the position of a lookup value within a specified cell range (which can be horizontal or vertical).

This formula is most commonly utilised alongside the INDEX formula. Click to learn how to use INDEX MATCH effectively.

Syntax

=MATCH(lookup_value,lookup_array,[match_type])

Arguments

  • Lookup_value – This is the value Excel will be looking for within the selected array. For instance in the example above, the lookup value is “C”.
  • Lookup_array – This is the range of cells in which Excel will search for the lookup value. This can either be a horizontal or vertical range, though it must only span a singular column (if vertical) or row (if horizontal).
  • Match_type (optional)
    • If 0, Excel will search for an exact match.
    • If 1, Excel will search for the largest value that is less than or equal to the lookup value. This is only useful when searching for numerical values.
    • If -1, Excel will search for the smallest value that is greater than or equal to the lookup value. This is only useful when searching for numerical values.
    • The default match type is 1 if the Match_type parameter is omitted.

Note: When the Match_type is set to zero; it is possible to perform a wildcard match. For instance, the formula =MATCH(“Test*”,A1:A4,0) would be able to recognise the value “Test-123” within a list due to the use of the asterisk wildcard.

Match formula not working?

There are a few common mistakes/things to check when your MATCH formula in Excel is not working.

For instance; when using the MATCH formula needs to match data type (i.e. text, number, date). Therefore, you should check that your lookup value and array are set to the same data type.

Another common mistake is to accidentally select more than 1 row/column for your lookup array. This will prevent match from operating as it can only provide the relative position of a value within a singular row/column.

Finally, it is also possible that the lookup_value you are searching for is not actually included within the lookup_array. To check this; copy the lookup_value and use the ‘Find’ function to search for the value within the array.

Have another issue?  Feel free to leave a comment and we will get back to you as soon as we can with a proposed solution.

Function category

Lookup and reference

Returns

The MATCH function will return the relative position of a lookup value within a specified range (i.e. whether it is the 1st, 2nd, 3rd or 4th result within a range).

error

Comments (No)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.