How to Use MATCH Function in Excel + Tips (Video in Khmer)

Hello everyone! Today i will show you how to use MATCH function in Microsoft Excel step by step.

What is MATCH Function in Excel?

The MATCH function is a Lookup formula in Excel that help you find the position number (row index or column index) of a specific item in a cell range.

⚠️ Important Note: MATCH formula does NOT return the actual item name or cell value itself. It only return the position number (example: 1, 2, 3...) where your search item is located inside the range!

📚 Related Tutorial: Want to combine MATCH with INDEX to replace VLOOKUP? Check out How to Use INDEX and MATCH in Excel!

🛠️ Formula Syntax & Parameters

The MATCH formula syntax look like this:

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value or text you want to search for (example: "Orange" or cell C2).
• lookup_array: The range of cells you want to search inside (must be 1 single column or 1 single row, like A2:A6).
• [match_type] (Optional):
  • 0 (Exact Match): Finds exact same value (Most popular! 99% of people use 0).
  • 1 (Less Than): Finds largest value smaller than or equal to lookup_value. (Requires data sorted A to Z / ascending).
  • -1 (Greater Than): Finds smallest value larger than or equal to lookup_value. (Requires data sorted Z to A / descending).

📋 Sample Data Table (Follow Along)

To try MATCH formula easily, create this simple list in cell range A1:A6:

📊 Simple Examples

Example 1: Find Position of Text (Exact Match)

We want to find the position number of "Orange" inside range A2:A6:

=MATCH("Orange", A2:A6, 0)

👉 Result: 3
(Because "Orange" is the 3rd item in range A2:A6: 1=Apple, 2=Banana, 3=Orange).

Example 2: Match Position in a Horizontal Row

MATCH also work across a horizontal row!

Create headers in row 1 (A1:D1): | ID | Name | Department | Salary |

Formula to find position of header "Department":

=MATCH("Department", A1:D1, 0)

👉 Result: 3
(Because "Department" is 3rd column header in range A1:D1).

🎥 MATCH Function Video Tutorial (Khmer)

Follow this video guide to watch step-by-step tutorial:

💡 Quick Tips for Using MATCH Function

1. Always Type , 0 for Exact Match: If you forget to type , 0 at the end, Excel defaults to 1, which can give wrong position number if list is not sorted alphabetically!
2. MATCH Work Only in 1D Range: lookup_array must be single row or single column (like A2:A10 or A1:E1). Do NOT select 2D table range like A1:C10.
3. Not Case Sensitive: MATCH does not care about capital letters. "APPLE", "apple", and "Apple" will give same result.
4. Combine with INDEX Formula: MATCH is super powerful when used inside INDEX(array, MATCH(...)) to do dynamic two-way lookups in any direction.
5. Handle #N/A Error: If MATCH cannot find your search item, it returns #N/A error. You can wrap it with =IFNA(MATCH(...), "Not Found") to hide error!

📚 Related Resources & Tutorials

• How to Use INDEX and MATCH in Excel: Step-by-Step Guide
• How to Use XLOOKUP Function in Excel
• VLOOKUP vs XLOOKUP in Excel: Simple Guide & Comparison
• How to Use IFNA Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗