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
| 1: | MATCH() will find either an exact match for lookup_value or, if the value is not in the range, it will find the largest value that is less than lookup_value. The list must be sorted in ascending order. If you do not enter a value for match_type, Excel will use 1 by default. |
| 0: | MATCH() will find an exact match for lookup_value. If the value is not in the range, it will return an #N/A error. The list is not required to be sorted. |
| –1: | MATCH() will find either an exact match for lookup_value or, if the value is not in the range, it will find the smallest value that is greater than lookup_value. The list must be in descending order. |
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:
- Find the column number with a MATCH() formula, as outlined above. (Be sure to include the first column of the data table.)
- 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.
