MATCH Function

Summary

The MATCH function searches for a value in a row or column and returns that value's relative position. If the item is the second entry in the range, MATCH returns 2. If it is the fifth entry, MATCH returns 5.

That makes MATCH useful when you need to know where something is, not just whether it exists. It is often paired with INDEX so one part of the formula finds the position and the other part returns the value from that position.

MATCH is useful because many lookup problems are really position problems first. Before a worksheet can return the right value, it often has to know where that value sits in a row or column, and MATCH is built exactly for that step.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value is the item you want to find, lookup_array is the one-dimensional range to search, and match_type controls how the match should work. A common example is =MATCH("Part-A", A1:A100, 0), which asks Excel to find the exact position of "Part-A" in that list.

MATCH works on a single row or a single column, not on a full two-dimensional table by itself. If you need a table lookup, MATCH is usually combined with INDEX, or replaced by a function such as XLOOKUP.

Arguments

• lookup_value - The value to search for. In exact-match mode, this can also use wildcards such as * and ?.
• lookup_array - The row or column where Excel should search.
• [match_type] - Optional. 0 means exact match, 1 means largest value less than or equal to the lookup value, and -1 means smallest value greater than or equal to the lookup value.

The safest choice for most everyday work is 0, because it asks for an exact match. The other two modes are useful, but they depend on sort order. With 1, the list should be sorted ascending. With -1, the list should be sorted descending. If the sort order is wrong, the result may be incorrect even if Excel does not show an error.

MATCH vs Other Functions

MATCH is mainly about returning a position, while some other lookup functions return the final value directly.

Using MATCH

The most common use of MATCH is inside an INDEX/MATCH formula. MATCH finds the position of a label, ID, or header, and INDEX uses that position to return the related value. This is one reason MATCH is so important: it gives other formulas a flexible way to locate the correct row or column.

MATCH is also useful on its own when position matters. You might need to know which item in a ranked list reached a target, which column contains a specific header, or whether an ID exists in a master list. In those cases, the position itself is useful information, even before another function gets involved.

Approximate matching can also be helpful when the list is sorted correctly. For example, a grading table or threshold table may need the position of the nearest allowed value rather than an exact text match. That is where 1 and -1 become useful, but only when the order of the list matches the selected mode.

• Use MATCH when you need a row or column position instead of the final value.
• Use 0 for most exact-match lookups.
• Use MATCH with INDEX for flexible lookups that are easier to maintain than hard-coded column numbers.

Example 1 - Find the Position of a Name

This formula searches the list and returns the position of "Manager_2". Since it is the second item in the range, MATCH returns 2. That position can then be reused in another formula, especially INDEX.

This is a good starting example because it makes the job of MATCH very clear. The function is not returning the name itself, only where that name sits in the list.

=MATCH("Manager_2", A1:A3, 0) // Returns 2

Example 2 - Search with a Wildcard

With match_type set to 0, MATCH can use wildcards. The pattern "SKU-202*" means "find text that starts with SKU-202". This is useful when the ending characters may vary but the beginning of the text is stable.

This makes the example practical for messy real data. You may not know the full code, but if the stable part is enough, MATCH can still help locate the item.

=MATCH("SKU-202*", A1:A3, 0)

Example 3 - Find the Position of a Header

MATCH is often used across a header row to find the column number of a label such as "Price". In this example, the result is 3 because "Price" is the third header in the row. This is a common step in two-way lookups.

That is useful when the return column should not be hard-coded. Instead of typing “column 3” manually, the formula can find where the header actually sits.

=MATCH("Price", A1:D1, 0) // Returns 3

Example 4 - Check Whether an Item Exists

MATCH returns a number when the item is found and #N/A when it is not. Wrapping it with ISNUMBER converts that behavior into a simple TRUE/FALSE test, which is a neat way to check whether an ID is present in a list.

This is practical when the workbook only needs a yes-or-no answer. Instead of using the position later, the formula turns the lookup into a quick existence check.

=ISNUMBER(MATCH(B5, A1:A3, 0)) // Returns TRUE if found

Conclusion Recap

MATCH is all about finding position. In this lesson, that position could be a name in a list, a header in a row, a wildcard text match, or just a quick check to see whether something exists.

That is why MATCH and INDEX work so well together. MATCH finds where the item is, and INDEX uses that number to return the related value, so each function does one clear job.

• Main job: MATCH returns the relative position of a value in a row or column.
• Safest mode: Use 0 when you need an exact match.
• Sort matters: 1 needs ascending data and -1 needs descending data.
• Common pairing: MATCH is often combined with INDEX to build flexible lookups.
• Extra benefit: In exact-match mode, MATCH can use wildcard patterns such as * and ?.