How to Use INDEX and MATCH in Excel with video tutorial in Khmer

In this tutorial, we will explore how to use the INDEX and MATCH functions together in Microsoft Excel. These two functions are incredibly powerful when used in combination, often referred to as the INDEX-MATCH pattern. This approach is highly flexible and efficient for retrieving specific values from a dataset.

The Power of INDEX-MATCH

While VLOOKUP is a popular choice for looking up values in Excel, it has one significant limitation: it can only look up values from left to right. This means the column you are searching in must be to the left of the column containing the data you want to retrieve. The INDEX-MATCH combination overcomes this limitation by allowing you to look up values in any direction—left, right, up, or down.

Why Use INDEX & MATCH?

• VLOOKUP can only look from left to right.
• INDEX and MATCH can look in any direction (left, right, up, down).

1. How the INDEX Formula Works

INDEX returns a cell value from a specific row and column position in a table.

=INDEX(array, row_num, [column_num])

• array: The group of cells to get your value from.
• row_num: The row number you want.
• column_num: The column number you want (Optional).

INDEX Video Tutorial (Khmer)

📚 Related Tutorial: Want to master Excel keyboard shortcuts? Check out Learn All Excel Keyboard Shortcuts ↗!

2. How the MATCH Formula Works

MATCH finds the position number of a value in a list.

=MATCH(lookup_value, lookup_range, match_type)

• lookup_value: The value you want to search for.
• lookup_range: The column or row to look in.
• match_type: Use 0 for an exact match.

MATCH Video Tutorial (Khmer)

📚 Related Tutorial: Learn more foundational formulas in our guide on Top 15 Basic Excel Formulas for Beginners ↗!

3. Combining INDEX and MATCH Together

Put MATCH inside INDEX to find a value automatically based on a search term:

=INDEX(C2:C100, MATCH("Product A", A2:A100, 0))

This finds "Product A" in column A, gets its row number, and returns the matching value from column C.

Example #1 INDEX & MATCH Video Tutorial in Khmer

Example #2 INDEX & MATCH Video Tutorial in Khmer

4. Two-Way (2D Grid) Lookups

You can combine a row MATCH and a column MATCH inside INDEX to look up data in a 2D matrix:

=INDEX(B2:G10, MATCH("Sales", A2:A10, 0), MATCH("Q3", B1:G1, 0))