Match Worksheet Function

by Linda Quinn

Close Window MATCH finds the location of a value in a specific row or column.

MATCH searches a row or column for the lookup_value.
It returns the cell location at the row OR column where the lookup value is found.
By itself, MATCH isn't terribly useful since MATCH only returns the cell location, not the cell value.

When combined with other functions, such as INDEX, it can be used to get around some of the limitations of VLOOKUP or HLOOKUP.

SYNTAX: =Match(lookup_value, lookup_array, [match_type])

Lookup_Value	This is the value you want to find. This can be a number, text, a cell reference, or a logical value.
Lookup_Array	This is a row or column where the match will be found. The lookup_array must be a single row or column.
Match_Type	0 = Find an exact match 1 = Closest value that is less than or equal to the lookup_value Lookup_array must be in ascending order 2 = Closest value that is greater than or equal to the look-up value Lookup_array must be in descending order

Example:
excel

=MATCH(39, B1:B4, 1)

Look in the lookup_array of B1:B4 for the value 39.
The match_type value is 1, which means it will find the exact number or the closest number that is less than 39.
The number 2 is returned by the MATCH formula because 37 is the second element in the lookup_array.

Example:
excel

=MATCH(39, B1:B4, 2)

Look in the lookup_array of B1:B4 for the value 39.
The match_type value is 2, which will find the exact number or the closest number that is greater than 39.
The number 3 is returned by the MATCH formula because 42 is the third element in the lookup_array.

Example:
excel

=MATCH("Gadgets", A1:A4, 0)

Look in the lookup_array of A1:A4 for the value Gadgets.
The match_type value is 0, which means to find the exact match.
The text in cell A3 is an exact match.
The value 3 is returned by the MATCH formula because Gadget is the third element in the lookup_array.

=================================================================
This content was created by Linda Quinn of LQNet.

See http://www.lqnet.com for a great collection of articles on this and other topics.

=================================================================

====================================================================
Want an expert to help with your project? LQ Systems, Inc. Business Solutions
====================================================================

Match Worksheet Function

Copyright © 2006-2008, LQ Systems,Inc. All rights reserved.