Nội Dung Bài Viết

In the vast ecosystem of Excel data analysis, efficiently locating specific data points is a fundamental skill. While most users are familiar with basic lookup functions, the MATCH function remains an unsung hero that provides critical utility. Unlike functions that retrieve the value of a cell, the MATCH function determines the relative position of an item within a range of cells.

Understanding how to leverage the MATCH function allows users to build more dynamic and resilient spreadsheets. It is rarely used in isolation; instead, its true power is unlocked when combined with other functions like VLOOKUP or INDEX. By mastering MATCH, you can automate column index numbers, perform two-way lookups, and significantly reduce errors caused by static references in your financial models or data reports.

Animation showing the selection of data range in Excel

Understanding the MATCH Function Syntax

Before diving into complex applications, it is essential to grasp the fundamental syntax of the function. The formula is designed to search for a specified item in a range of cells and then return the relative position of that item in the range.

The standard syntax is as follows:

=MATCH(lookup_value, lookup_array, [match_type])

Here is a detailed breakdown of the arguments:

lookup_value (Required): The value you want to locate in the list. This can be a specific number, text string, logical value, or a cell reference containing the value.
lookup_array (Required): The range of cells being searched. This can be a row or a column.
match_type (Optional): A number that specifies how Excel matches the lookup_value with values in the lookup_array.
- 1 (or omitted): Finds the largest value that is less than or equal to the lookup_value. The lookup_array must be placed in ascending order.
- 0: Finds the first value that is exactly equal to the lookup_value. This is the most common setting and requires no sorting.
- -1: Finds the smallest value that is greater than or equal to the lookup_value. The lookup_array must be placed in descending order.

A Basic Step-by-Step Example

Let’s look at a practical scenario. Suppose you have a list of product codes in column A (A2:A11), and you need to find the position of the code “SGK07”.

You would use the following formula:

=MATCH(E1,A2:A11,0)

In this example, E1 contains the value “SGK07”, and we use 0 for an exact match.

Xem thêm: Mastering Linked Data Types in Excel 365: A Comprehensive Guide

Excel spreadsheet showing the MATCH formula returning the row position of a product code

The formula returns 7. This indicates that “SGK07” is the 7th item from the top in the selected range A2:A11. It is important to remember that the result refers to the position within the selection, not the Excel row number (unless the selection starts at A1).

Critical Rules to Remember

To avoid common errors like #N/A or incorrect data return, keep these characteristics in mind:

Position, Not Value: MATCH never returns the content of the cell; it only returns the numeric position (integer).
Case Insensitivity: By default, MATCH does not distinguish between uppercase and lowercase letters (e.g., “TEXT” and “text” are treated as identical).
Duplicates: If the lookup_array contains duplicate values, an exact match type (0) will return the position of the first instance it encounters.
Error Handling: If the function cannot find the lookup_value, it returns the #N/A error. This is often useful for data validation to check if an item exists.

Advanced Techniques: Using Wildcards

When working with text strings, you might not always know the exact spelling, or you might need to find a pattern. The MATCH function supports wildcards when the match_type is set to 0.

Question mark (?): Matches any single character.
*Asterisk (``)**: Matches any sequence of characters (zero or more).

Example 1: Finding a prefix
To find the position of the first item that starts with “Car”, you can use the asterisk wildcard:

=MATCH(E1&"*",A2:A11,0)

If E1 contains “Car”, the formula looks for “Car” followed by anything.

Excel example demonstrating wildcard search with MATCH function

In the dataset shown above, the result is 2, corresponding to the first item matching the pattern.

Example 2: Finding a specific pattern
If you are looking for a 5-letter word starting with “ba” and ending with “er”, you would use:

=MATCH("ba?er",A2:A11,0)

This would match a value like “Baker” and return its relative position (e.g., 5).

Performing a Case-Sensitive Match

Since the standard MATCH function ignores case, distinguishing between “Apple” and “apple” requires a workaround using the EXACT function. This combination creates an array formula.

The syntax is:

=MATCH(TRUE,EXACT(lookup_value,lookup_array),0)

Note: If you are not using Excel 365 or Excel 2021, you must press Ctrl + Shift + Enter to execute this as an array formula. Curly braces {} will appear around the formula.

Spreadsheet showing a case-sensitive MATCH formula using the EXACT function

This technique compares the lookup_value against every item in the array exactly, returning TRUE only where the cases match perfectly.

Xem thêm: Cách Tính Thời Hạn Hợp Đồng trong Excel

comparing Two Data Columns

A common data cleaning task is comparing two lists to identify missing items. By combining MATCH with the ISNA function, you can quickly flag items that exist in one list but not the other.

Formula logic:

=IF(ISNA(MATCH(lookup_value,lookup_array,0)),"Not in List","Found")

If MATCH returns #N/A (meaning the value wasn’t found), ISNA evaluates to TRUE, and the IF function returns your custom message.

Excel sheet comparing two columns using ISNA and MATCH

The Power Combo: VLOOKUP + MATCH

The most powerful application of MATCH is nested inside a VLOOKUP function. A standard VLOOKUP requires a static “column index number” (e.g., 3 for the third column). However, if you insert a new column into your dataset, your VLOOKUP breaks because the static number 3 is no longer pointing to the correct data.

By replacing the static number with a MATCH function, you create a dynamic VLOOKUP.

How it works

Instead of typing “3”, you use MATCH to find the column header’s position.

=VLOOKUP(lookup_value, table_array, MATCH(column_header, header_row, 0), FALSE)

Let’s look at the setup. Below, we want to look up a value based on a changing column header.

Setup for dynamic VLOOKUP showing data table and lookup fields

In this scenario, we use MATCH to find which column number corresponds to the header we are interested in.

Selecting the header row for the MATCH function inside VLOOKUP

The complete dynamic formula looks like this:

=VLOOKUP(G1, $B$2:$D$11, MATCH($F$2, $B$1:$D$1, 0), FALSE)

Here, MATCH($F$2, $B$1:$D$1, 0) looks at the header name in cell F2 and finds its position in the header row B1:D1. If F2 says “Price”, MATCH returns 3. If you change F2 to “Quantity”, MATCH automatically updates to return 2.

The result of the VLOOKUP and MATCH combination

This method essentially creates a two-way lookup (Matrix Lookup), allowing you to find a value at the intersection of a specific row and a specific column.

Another example of VLOOKUP and MATCH working together seamlessly

Conclusion

The MATCH function is a versatile tool that goes far beyond simple position finding. Whether you are validating data existence, performing complex wildcard searches, or building robust, dynamic lookup formulas with VLOOKUP, MATCH is an essential component of advanced Excel proficiency.

By mastering the combination of VLOOKUP and MATCH, you ensure your spreadsheets remain accurate even when the structure of your data changes. We hope this guide has provided you with a clear understanding and practical applications to enhance your daily workflow.