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

the table.

Using the

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