Microsoft Excel MATCH Function Help

MATCH Function Help

•  Click to view Match function examples

MATCH Function Definition

Returns the position (index) of an item in an array or range that matches a specified value. Use MATCH on a single row or column of table to find the position of name, number, date...etc. Remember though the position returned is relative to the range which means if you look at a range C3:C10 and find widget3 in it, MATCH returns 1 because cell C3 is the first cell in the range being scanned. If the range started at C1 then it would return 3 because it is the 3 cell down. This function can be used in combination with the INDEX function to create a powerful VLOOKUP type function that hunts information down in both column and rows at once.

MATCH Function Syntax

=MATCH(lookup_value,lookup_array,[match_type])

[] bracket means argument is optional which means you do not have to fill the slot.

MATCH Function Argument Definitions

lookup_value: This is the value (name, number, date) you use to find the value you want in a column or row of a table.

• The lookup_value argument is the value you want to match in a column or row of a table
• The lookup_value argument can be a number, text, date or logical value or a cell reference. Note that if a date is used, first convert its number format.

lookup_array: This is a contiguous range of cells, row or column, containing possible lookup values. It can also be an array.

match_type: This can be three preset numbers, -1, 0 or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array. Do not default this value, always include it!!!! If you are searching for a specific value which is the case in most instances, always put 0 or you might end up with the wrong match.

• If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The row or column scanned 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. The row or column scanned can be in any order.
• If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The row or column scanned 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. Do not do this!

MATCH Function Tips

• 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"}. For eaxmple, MATCH("Widget1",A1:A10,0), match would return 2 if cell A2 had Widget1 in it.
• Match is not upper and lower case sensitive.
• Match returns an #N/A if it cannot find an answer.
• 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.