Microsoft Office Tutorials and References
In Depth Information
Using the MATCH Function to Locate the Position of a Matching Value
22. Ensure that the lookup table is in $G$3:$L$8. Excel looks through the
first row of this table to find the matching year.
33. When the matching column is found, you want Excel to return the date
for Easter. Although this is in row 5 of the worksheet, it is in the
third row of the table, so ensure that the third parameter for the
function is 3.
44. Your years are already sorted left to right, but if you use a value
of TRUE for the fourth parameter, this causes problems in the year
2014, so make the fourth parameter FALSE. Ensure that the formula in
cell B6 is =HLOOKUP($B$2,$G$3:$L$8,3,FALSE).
55. Copy this formula to cell B11 and edit the formula to change the third
parameter from 3 to 4 because the Fourth of July is the fourth row of
Using the MATCH
Function to Locate the Position of a Matching Value
At first glance, MATCH seems like a function that would rarely be useful.
MATCH returns the relative position of an item in a range that matches a
specified value in a specified order. You use MATCH instead of one of the
lookup functions when you need the position of an item in a range instead of
the item itself.
Suppose that your manager asks, “ Can you tell me on which row I would find
this value? ” The manager wants to know the value or some piece of data on
that record. However, the manager rarely wants to know that XYZ is found
on the 111th relative row within the range A99:A11432.
MATCH comes in handy in several instances. In the first instance, consider
a situation in which you are using VLOOKUP to find whether an item is in a
list. In this case, you do not care what value is returned. You are either in-
terested in seeing if a valid value is returned, meaning that the entry is in the
old list, or if an #N/A is returned, meaning that the entry is new. In this case,
using MATCH is a slightly faster way to achieve the same result.
Another handy way to use MATCH is with the INDEX function. MATCH has
two features that make it more versatile than VLOOKUP. MATCH allows
for wildcard matches. MATCH also allows for a search based on an exact
match, based on the number just below the value, or based on a value greater
than or equal to the lookup value. This third option is not available in the
VLOOKUP or HLOOKUP functions.
MATCH Function to Locate the Position of a Matching Value