How to Use MATCH in Excel
MATCH returns the position of a value inside a row or column. Use it when a lookup needs the row number, column number, or field position.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
MATCH returns the position of a value inside a single row or column. It does not return the value itself. It gives you the number another formula can use, usually INDEX.
Use MATCH when you need to find where a month, account, client, status, or header appears in a list.
The syntax
=MATCH(lookup_value, lookup_array, [match_type])- lookup_value - the value you want to find
- lookup_array - the one-row or one-column range to search
- [match_type] - use
0for exact match,1for approximate ascending match, or-1for approximate descending match
For most operating sheets, exact match is the one you want.
=MATCH("March", B1:M1, 0)That returns the position of March inside the header row.
Example: find the month column in a budget
Suppose row 1 contains month headers from January through December, and column A contains budget categories.
You want a formula that can find the column position for the month selected in cell P1.
Step 1. Put the selected month in a control cell
Cell P1 contains:
April
Step 2. Match that month against the header row
=MATCH(P1, B1:M1, 0)If April is the fourth month in the range B1:M1, the formula returns 4.
Step 3. Use the position inside INDEX
If the Marketing budget row is B6:M6, this returns the Marketing value for the selected month:
=INDEX(B6:M6, MATCH(P1, B1:M1, 0))Now the selected month can change without rewriting the formula.
✦ TIP
0 as the match type when matching names, labels, months, statuses, or IDs.MATCH vs XLOOKUP
XLOOKUP is usually easier when you want the result directly. MATCH earns its keep when the thing you need is a position:
- which column contains the selected month
- which row contains a client ID
- where a field appears after an export changes column order
- which item is first in a ranked list
It also pairs well with INDEX in workbooks that need to stay compatible with older Excel setups.
Common issues
| Issue | Cause | Fix |
|---|---|---|
#N/A | The lookup value is not found | Check spelling, spaces, and data type |
| Wrong position | Approximate match was used accidentally | Use 0 for exact match |
#VALUE! | The lookup array is two-dimensional | Use one row or one column only |
| Unexpected miss | One value is text and the other is a number | Normalize the source values |
The Griddy way
MATCH is simple until an export changes column order and half the workbook starts pointing at the wrong field.
"Find the column named April and use it to return the budget value for each category"
Griddy can build the MATCH or INDEX MATCH logic, apply it across the rows, and keep the formulas tied to the header labels instead of hard-coded positions.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Use MATCH when reports need flexible positions
MATCH is most useful in budget and billing sheets where month headers, account names, or exported field positions can move.
Budget Tracker
Track income, expenses, and savings in one place. Line items, budgeted vs actual totals, and monthly net savings — free to use in your browser.
Small Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.
Small Business Budget for Retail
Plan retail revenue, inventory costs, payroll, rent, marketing, software, and operating margin with a free budget spreadsheet template.
Invoice Template
Professional invoice template with automatic subtotal, tax, and total calculations. Customise with your logo and send in minutes.