How MATCH works in Excel? Best MATCH examples

How MATCH works in Excel? Best MATCH examples

One of my favorite Excel functions is MATCH. It’s incredibly useful, especially when working with large datasets. If you’ve ever wondered, “How MATCH works in Excel? Best MATCH examples,” then you’re in the right place. Let’s break it all down.

What Does the MATCH Function Do?

The MATCH function in Excel searches for a specific value in a range and returns the relative position of that value. Unlike VLOOKUP or HLOOKUP, which return the actual value from a table, MATCH only gives you the position.

MATCH Function Syntax

Here’s the syntax:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value – The value you’re searching for.
  • lookup_array – The range where you’re searching.
  • match_type (optional) – Determines how the function should match the lookup_value.

The match_type parameter can take three values:

  • 1 (default) – Finds the largest value less than or equal to lookup_value.
  • 0 – Finds the exact match.
  • -1 – Finds the smallest value greater than or equal to lookup_value.

Basic Example of MATCH

Let’s say I have a list of numbers and want to find the position of a specific value.

Number List:
A1: 10
A2: 20
A3: 30
A4: 40
A5: 50

Formula:
=MATCH(30, A1:A5, 0)

The function returns 3 because 30 is in the third position in the range.

Using MATCH with Different Match Types

Let’s explore different match_type values in more detail.

Exact Match (match_type = 0)

If I specify 0 as the match type, MATCH will only return a position if the value exists in the array.

=MATCH(25, A1:A5, 0)

This formula will return an #N/A error since 25 is not in the list.

Approximate Match (match_type = 1)

When using 1, Excel assumes the list is sorted in ascending order. If the exact value isn’t found, Excel returns the position of the largest value that is less than or equal to the lookup value.

=MATCH(25, A1:A5, 1)

The function returns 2 because the largest number less than 25 is 20, which is in position 2.

Reverse Approximate Match (match_type = -1)

Using -1 assumes the list is sorted in descending order. Excel will return the position of the smallest number greater than or equal to the lookup value.

Descending List:
A1: 50
A2: 40
A3: 30
A4: 20
A5: 10

Formula:
=MATCH(25, A1:A5, -1)

The function returns 3 since 30 is the smallest number greater than or equal to 25.

Combining MATCH with Other Functions

The real power of MATCH comes when you combine it with other functions like INDEX. Let’s see how.

Using MATCH with INDEX

One of the most common uses of MATCH is in combination with INDEX. Here’s a practical example:

A B
Apples 100
Bananas 200
Cherries 300

If I want to find how many Bananas we have, I can use:

=INDEX(B1:B3, MATCH("Bananas", A1:A3, 0))

This formula finds the position of “Bananas” in column A, then uses that position to extract the corresponding value from column B: 200.

Common Errors in MATCH

Like every Excel function, MATCH comes with its share of pitfalls. Here are a few common issues:

  1. #N/A Error – Happens when lookup_value isn’t found or the data isn’t sorted for approximate matches.
  2. #VALUE! Error – Occurs if lookup_array contains different data types.
  3. Incorrect Sorting – When using 1 or -1, ensure your data is appropriately ordered.

Final Thoughts

Understanding how MATCH works in Excel can save you a ton of time when analyzing data. Whether used alone or in combination with INDEX, it’s an essential function for dynamic data lookup. Next time you find yourself manually searching for a position in a dataset, give MATCH a try!

 

Other interesting article:

How INDEX works in Excel? Best INDEX examples