Friday, 27 July 2012

Excel MATCH Function Syntax and Example

Microsoft Excel MATCH Function
Returns the relative position of an item in an Array/Range of data that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
Syntax :
LookupValue is the value you use to find the value you want in a table or given range of data.
LookupValue is the value you want to MATCH in LookupArray.
For example, when you look up an Employee ID number in a list, you are using the Employee name as the lookupValue, but the ID number is the value you want.
LookupValue can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
LookupArray  is a contiguous range of cells containing possible lookup values. LookupArray must be an array or an array reference.
MatchType is the number -1, 0, or 1. MatchType specifies how Microsoft Excel matches LookupValue with values in LookupArray.
      If  MatchType is 1, MATCH finds the largest value that is less than or equal to LookupValue. LookupArray must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
      If  MatchType is 0, MATCH finds the first value that is exactly equal to LookupValue. LookupArray can be in any order.
    If MatchType is -1, MATCH finds the smallest value that is greater than or equal to LookupValue. LookupArray must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
      If MatchType is omitted, it is assumed to be 1.
Example :
For example, If you want to look up the position of an Prod_Id(B7=789 which is LookupValue)then you can do by the following way.

=MATCH(F4,B1:B10,0) will returns the 7

MATCH returns the position of the matched value within LookupArray, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.
MATCH does not distinguish between uppercase and lowercase letters when matching text values.
If MATCH is unsuccessful in finding a MATCH, it returns the #N/A error value.
If MatchType is 0 and LookupValue is text, you can use the wildcard characters, question mark (?) and asterisk (*), in LookupValue. A question mark matches any single character; an asterisk MATCHes any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts