Free Online Tutorials on Spreadsheets

MATCH function

The MATCH function looks up a value from an array and returns the relative position of the lookup value. For example, if the range B4:B6 contains the values 8,9 and 12 then the formula =MATCH(12,B4:B6,0) returns the relative position of 3 of the lookup value 12.

Syntax

The MATCH function has the following arguments:

MATCH(lookup_value, lookup_array, [match_type])

Lookup_value – Required. The value to lookup for the relative position.

Lookup_array – Required. The data range to be searched for the relative position.

[match_type] – Optional. The number -1, 0, or 1 uses to specify how to match lookup_value.

Match_typeBehavior
1 or omittedMATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
0MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.

Example

Result.

For more detail and information, visit Microsoft Office official website.




Copyright © 2016 - 2019 Explainry.com | All Rights Reserved