WorksheetFunction.Match(Object, Object, Object) Method
Definition
Important
Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.
Returns the relative position of an item in an array that matches a specified value in a specified order. Use Match instead of one of the Lookup(Object, Object, Object) functions when you need the position of an item in a range instead of the item itself.
public double Match (object Arg1, object Arg2, object Arg3);Public Function Match (Arg1 As Object, Arg2 As Object, Optional Arg3 As Object) As DoubleParameters
- Arg1
- Object
Lookup_value - the value you use to find the value you want in a table.
- Arg2
- Object
Lookup_array - a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.
- Arg3
- Object
Match_type - the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.
Returns
Remarks
Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
If match_type is 1, Match finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, false, true.
If match_type is 0, Match finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
If match_type is -1, Match finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: true, false, Z-A, ...2, 1, 0, -1, -2, ..., and so on.If match_type is omitted, it is assumed to be 1.
Match returns the position of the matched value within lookup_array, 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 match_type is 0 and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. 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.