How to Use MATCH in Excel

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

• lookup_value - the value you want to find
• lookup_array - the one-row or one-column range to search
• [match_type] - use 0 for exact match, 1 for approximate ascending match, or -1 for approximate descending match

For most operating sheets, exact match is the one you want.

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

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:

Now the selected month can change without rewriting the formula.

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

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.

A reliable audit sequence

Use MATCH as a position finder, then make the return step visible. First confirm that the lookup value and lookup range use the same type: a number stored as text will not behave like a numeric value in an exact match. Next use 0 unless you intentionally need an approximate banding lookup. Finally, pair the position with INDEX when you need the related value:

If the formula returns #N/A, test whether the key really exists and whether extra spaces or inconsistent capitalization are involved. If the key can be absent, wrap the result with IFNA and show a review label rather than masking every error with a blank. This keeps missing data distinguishable from a legitimate blank result.

Sources

• Microsoft Support: MATCH function
• Microsoft Support: INDEX function