How to Use the Excel Match Function to Find Data

In Excel, some functions exist to be used to increase functionality when paired with other functions. MATCH is a good example. MATCH helps you search a range of references to find a “match” to your query. It then returns the position of that match. In short, you can use MATCH to look for your data in a specified location and determine where that match exists.

Imagine for instance, you’re reviewing the monthly sales figures by state. For your upcoming meeting, you need to show the over/under compared to the budget amount from the company’s forecast. What is the budgeted amount for Georgia in August? Pretty specific, yes?

It’s simple enough to find the August row and scan over to the Georgia column, but how do you write an Excel formula to find that value for you? The formula would be simple using a VLOOKUP() for the month of August if you know which column Georgia figures are in. Here lies the problem. You could find the Georgia column manually and type it into the VLOOKUP formula, but you’d have to do that manual process all over again for each state—and the likelihood of human error increases each time!

The trick is to use the MATCH() function in your formula. The next section covers the basics of how to use the function; following sections provide examples combining MATCH() with VLOOKUP().

How to use MATCH()

MATCH() calls for three parameters:

• Lookup_value: the value to find;
• Lookup_array: the range of cells or array of values to search; and
• Match_type: the type of search to perform. (See below for “Values for Match_type”)

So, to find the Georgia column, use the formula “=MATCH(“Georgia”, $A$1:$G$1, 0)”.

You could use “=MATCH($B$15, $A$1:$G$1, 0)” to add flexibility. This formula allows the user to type a state in cell B15 and that value is read by the MATCH() function.

If you intend to use this with a VLOOKUP, be sure to include Column A in your range.

Values for Match_type

The Match_type portion of the function tells the formula what type of search to perform

Combining MATCH() with VLOOKUP()

The idea behind combining these two formulas is this: VLOOKUP() requires a column number, and MATCH() finds the column number.

To write the combined lookup:

1. Find the column number with a MATCH() formula, as outlined above. (Be sure to include the first column of the data table.)
2. Use that value as the column parameter for VLOOKUP.

You can also write the formula in one step: “=VLOOKUP(B18, $A$1:$G$13, MATCH(B15, $A$1:$G$1, 0), FALSE)”. If you choose this option, however, keep in mind that you are more likely to introduce errors in complex formulas and they are more difficult for later users to decipher. It’s often a good idea to keep the formulas separate, even if they’re hidden on a non-printing portion of the page.

Next Steps

For even more flexibility, try using INDEX() for your lookups. You’ll need a MATCH() formula to find the row number and another MATCH() to find the column number.

Once you become familiar with these tools, you’ll have complete flexibility with your lookups—even if the data table itself is selected by formula, too.