Kornilios Ampatzis/ April 18, 2019/ Functions

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

We have prepared a tutorial on the MATCH function to explain it in a bit more detail.

The syntax of the function is the following:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value : The value that you want to match in lookup_array.
lookup_array : The range of cells being searched.
match_type: Optional. The number -1, 0, or 1.
  • The default value is 1. It finds the largest value that is less than or equal to lookup_value.
  • The value 0 finds the first value that is exactly equal to lookup_value.
  • The value -1 finds the smallest value that is greater than or equal to lookup_value.

MATCH return the position of the matched value and not the value itself

MATCH is case insensitive when matching text values

If no match is found an error #N/A is returned.

You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Click on the button to practice using this function, with the help of our Online Assessment Tool:

Practice

Here is an example of how to use the MATCH function:

Use the proper formula to match the position of the table where the products of the cells E7:E8 are found, and show the respected values in the cells F7:F8.

Share this Post