|
|
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.
|