MATCH

Returns the position of an item in the specified list that matches the specified value in the specified order.

Syntax

MATCH( Any lookup, ReferenceOrArray searched, [Integer matchType = 1] ) → Any

Parameters

lookup

Required. The value to look for in the searched list.

searched

Required. The cell reference or the array that should be searched that contains only one row or column.

matchType

Optional. A value indicating how to match lookup with the values in searched. If omitted, matchType is assumed to be 1.

Remarks

MATCH searches for lookup in searched and returns its relative position in the list. If lookup could not be found, the function returns the #N/A error value.

If matchType is -1, MATCH finds the smallest value that is greater than or equal to lookup. The values in searched are expected to be sorted in descending order, texts before numbers (for example, TRUE, FALSE, "D", "B", "A", 1, 0, -2). From a sequence of identical values greater than or equal to lookup, the last value is selected.

If matchType is 0, MATCH finds the first value that is equal to lookup. The values in searched do not need to be sorted.

If matchType is 1 (or omitted), MATCH finds the largest value that is less than or equal to lookup. The values in searched are expected to be sorted in ascending order, numbers before texts (for example, -2, 0, 1, "A", "B", "D", FALSE, TRUE). From a sequence of identical values less than or equal to lookup, the last value is selected.

Use MATCH instead of one of the LOOKUP functions when you need the position of an item in the list rather than the item itself.

See Also

Function Reference
HLOOKUP Function
VLOOKUP Function
INDEX Function
OFFSET Function