How to Use Match Function in Google Sheets – Examples

0
105
Match Function in Google Sheets

When you independently use MATCH function in Google Sheets , it has only one role to do. It’s to return the relative position of an item in a single row or column that you specify. You can extend the functionality of Match function, in combination with INDEX function.

Just take a look at the syntax below and then we can directly move to Match function examples in Google Sheets.

Match Function in Google Sheets – Syntax

MATCH(search_key, range, search_type)

Search Key: It’s the value to search in a range. It can be text string, numeric value or date.

Range: It must be a single row or single column. For example A2:A10, or A2:G2. That means a vertical or horizontal range.

Search Type: This element is optional. By default the value is 1. Zero means, the range is not sorted and One means the ranges is sorted in ascending order. To indicate descending order you can use “-1”.

Now let’s move to the examples to the use of MATCH function in Google Sheets

How to Use Google Sheets Match Function

Here we can see few examples of Google Sheets Match function on Vertical ranges or single column.

Examples:

1. Match Function Example with Text String as Search Key

Note: Please refer the syntax given above to understand what is “Search Key.”

Match Function Example with Text String as Search Key

In the above example, on the first formula, in cell J2, you can see the Search Key. The Range reference is C2:C12 and I’ve put ‘0’ as Search Type to indicate no sorting. Here as you can see, I’ve used text string as Search Key. In the below examples, I’ve used numeric as well as date values as Search Key.

In the above Match function example, “Coverall” is the search key. It’s relative position to the selected range is 2. So the result is 2.

2. Match Function Example with Numeric Value as Search Key

I am not going to any details here. The description just under the above image is also applicable to this example too. Only changes is the search key used in the below function, which is numeric.

Match Function Example with Numeric Value as Search Key

3. Match Function Example with Date as Search Key

Match Function Example with Date as Search Key

All the above are the examples of vertical use of Google Sheets Match function. Same way you can select the range horizontally. But only single row or single column is acceptable.

Finally one more small example.

Error in match function in Google Sheets

In the above example, Search Key 4 is missing in the range “J15:J20”. The second Match function with search type 1 returns its to be┬árelative position in the rage, while the first formula that with search type 0 returns error!

I am leaving one task for you to do. Test the MATCH formula with a range where same value repeats multiple times!

LEAVE A REPLY

Please enter your comment!
Please enter your name here