match

 The MATCH function in Excel is used to find the position of a specified value within a range of cells. It returns the relative position of the item in the range. Below are 10 examples of how to use the MATCH function, including data and formulas.

Example 1: Basic MATCH Function

Data:

Copy

A1: Apple
A2: Banana
A3: Cherry
A4: Date

Formula:

excel

Copy

=MATCH("Banana", A1:A4, 0)

Result:

Copy

2

Explanation: The formula searches for "Banana" in the range A1:A4 and returns its position, which is 2.

Example 2: MATCH with Wildcard

Data:

Copy

A1: Apple
A2: Banana
A3: Cherry
A4: Date

Formula:

excel

Copy

=MATCH("B*", A1:A4, 0)

Result:

Copy

2

Explanation: The formula uses a wildcard to find the first item starting with "B", which is "Banana" at position 2.

Example 3: MATCH with Approximate Match

Data:

Copy

A1: 10
A2: 20
A3: 30
A4: 40

Formula:

excel

Copy

=MATCH(25, A1:A4, 1)

Result:

Copy

2

Explanation: The formula finds the position of the largest value less than or equal to 25, which is 20 at position 2.

Example 4: MATCH with Exact Match

Data:

Copy

A1: 10
A2: 20
A3: 30
A4: 40

Formula:

excel

Copy

=MATCH(30, A1:A4, 0)

Result:

Copy

3

Explanation: The formula finds the exact match for 30, which is at position 3.

Example 5: MATCH with Horizontal Range

Data:

Copy

A1: 10  B1: 20  C1: 30  D1: 40

Formula:

excel

Copy

=MATCH(30, A1:D1, 0)

Result:

Copy

3

Explanation: The formula searches for 30 in the horizontal range A1:D1 and returns its position, which is 3.

Example 6: MATCH with Dynamic Range

Data:

Copy

A1: 10
A2: 20
A3: 30
A4: 40

Formula:

excel

Copy

=MATCH(MAX(A1:A4), A1:A4, 0)

Result:

Copy

4

Explanation: The formula finds the position of the maximum value in the range A1:A4 , which is 40 at position 4.

Example 7: MATCH with INDEX Function

Data:

Copy

A1: Apple
A2: Banana
A3: Cherry
A4: Date

Formula:

excel

Copy

=INDEX(A1:A4, MATCH("Cherry", A1:A4, 0))

Result:

Copy

Cherry

Explanation: The MATCH function finds the position of "Cherry", and the INDEX function returns the value at that position.

Example 8: MATCH with Multiple Criteria

Data:

Copy

A1: Apple   B1: Red
A2: Banana  B2: Yellow
A3: Cherry  B3: Red
A4: Date    B4: Brown

Formula:

excel

Copy

=MATCH(1, (A1:A4="Cherry")*(B1:B4="Red"), 0)

Result:

Copy

3

Explanation: The formula uses an array to match both "Cherry" and "Red", returning the position 3.

Example 9: MATCH with LOOKUP Function

Data:

Copy

A1: 10
A2: 20
A3: 30
A4: 40

Formula:

excel

Copy

=LOOKUP(MATCH(25, A1:A4, 1), A1:A4)

Result:

Copy

20

Explanation: The MATCH function finds the position of the largest value less than or equal to 25, and the LOOKUP function returns the corresponding value, which is 20.

Example 10: MATCH with IF Function

Data:

Copy

A1: Apple
A2: Banana
A3: Cherry
A4: Date

Formula:

excel

Copy

=IF(MATCH("Banana", A1:A4, 0) > 2, "Not in Top 2", "In Top 2")

Result:

Copy

In Top 2

Explanation: The MATCH function finds the position of "Banana", and the IF function checks if it is within the top 2 positions.

These examples demonstrate the versatility of the MATCH function in Excel, showing how it can be used in various scenarios to find the position of a value within a range.