Microsoft Excel MATCH Function Help
EMAGENIT Home page

Call us at:   1.805.498.7162     1.805.558.9277--

Learn about how we can help your company or yourself achieve your Microsoft Excel training or self-study goals Visit our free Microsoft Excel and Microsoft Excel VBA resource center Learn how we can help save your company money with our innovative software solutions Visit out VBA code center for code that can save your company money Learn about our Excel Add-In software that can save your company time and money View our customer list from our 12 years in business Phone numbers, request form and email address Learn about EMAGENIT View our training schedule for Microsoft Excel and Microsoft Excel VBA

 

MATCH Function Help

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.