**π Part 1: Introducing the MATCH Function in Excel π**

π **Definition** The MATCH function in Microsoft Excel is a powerful lookup function that allows you to find the relative position of a specified value within a range of cells. It returns the position of the first occurrence of the lookup value in the specified range based on the chosen match type.

π **Purpose** The Purpose of the MATCH function is to help you locate the position of a specific value in a list or array of data. It is commonly used with other functions like INDEX, VLOOKUP, HLOOKUP, and others to retrieve corresponding values or perform lookups.

π **Syntax & Arguments** The syntax of the MATCH function is as follows:

`=MATCH(lookup_value, lookup_array, [match_type]) `

π **Explain the Arguments in the Function**

`lookup_value`

: The Value you want to find within the`lookup_array`

. This is the Value you are searching for.`lookup_array`

: The range of cells where you want to search for the`lookup_value`

. It can be a row, a column, or a one-dimensional array.`match_type`

(optional): The match type is an optional argument that determines how the Function handles the lookup_value:`0`

(Exact Match): The Function searches for an exact match. It returns the position of the first Value that exactly matches the lookup_value.`1`

(Less Than or Equal To): The Function searches for the largest value that is less than or equal to the lookup_value. The lookup_array must be sorted in ascending order.`-1`

(Greater Than or Equal To): The Function searches for the smallest value greater than or equal to the lookup_value. The lookup_array must be sorted in descending order. If omitted, the default match_type is`1`

.

π **Return Value** The MATCH function returns the relative position of the `lookup_value`

within the `lookup_array`

.

π **Remarks**

- If the
`lookup_value`

is not found in the`lookup_array`

, the Function returns the`#N/A`

error. - When using the optional
`match_type`

, ensure that the`lookup_array`

is sorted in the specified order to get correct results.

π Part 2: Examples of Using the MATCH Function in Excel π

π **Example 1: Searching for Employee ID (Sheet: “EmployeeData”)**

Table 1 (Employee Data Table) – Sheet: “EmployeeData”

A | B | C | |
---|---|---|---|

1 | Employee ID | Name | Department |

2 | 101 | John | Sales |

3 | 102 | Mary | Marketing |

4 | 103 | Bob | Finance |

Table 2 (MATCH Result) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Search Value | Position of Employee ID in the List |

2 | 102 | =MATCH(A2, EmployeeData!$A$2:$A$4, 0) |

3 | 104 | =MATCH(A3, EmployeeData!$A$2:$A$4, 0) |

4 | 101 | =MATCH(A4, EmployeeData!$A$2:$A$4, 0) |

*Explanation:* In this example, we want to find the position of the specified Employee ID in the list using the MATCH function. The formula in cell B2 would be “=MATCH(A2, EmployeeData!$A$2:$A$4, 0)”. The function searches for the value “102” (A2) within the Employee ID column (A2:A4) and returns the relative position, which is 2. If the Employee ID is not found (A3 and A4), the Function will return the `#N/A`

error.

π **Example 2: Searching for Product Name (Sheet: “Products”)**

Table 1 (Product Data Table) – Sheet: “Products”

A | B | C | |
---|---|---|---|

1 | Product ID | Product Name | Price |

2 | 001 | Laptop | $800 |

3 | 002 | Smartphone | $600 |

4 | 003 | Tablet | $400 |

Table 2 (MATCH Result) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Search Value | Position of Product Name in the List |

2 | Smartphone | =MATCH(A2, Products!$B$2:$B$4, 0) |

3 | Headphones | =MATCH(A3, Products!$B$2:$B$4, 0) |

4 | Laptop | =MATCH(A4, Products!$B$2:$B$4, 0) |

*Explanation:* In this example, we want to find the position of the specified Product Name in the list using the MATCH function. The formula in cell B2 would be “=MATCH(A2, Products!$B$2:$B$4, 0)”. The Function searches for the value “Smartphone” (A2) within the Product Name column (B2:B4) and returns the relative position, which is 2. If the Product Name is not found (A3 and A4), the Function will return the `#N/A`

error.

π **Example 3: Searching for Sales Amount (Sheet: “SalesData”)**

Table 1 (Sales Data Table) – Sheet: “SalesData”

A | B | C | |
---|---|---|---|

1 | Product ID | Product Name | Sales |

2 | 001 | Laptop | $25,000 |

3 | 002 | Smartphone | $32,000 |

4 | 003 | Tablet | $18,000 |

Table 2 (MATCH Result) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Search Value | Position of Sales Amount in the List |

2 | $32,000 | =MATCH(A2, SalesData!$C$2:$C$4, 0) |

3 | $20,000 | =MATCH(A3, SalesData!$C$2:$C$4, 0) |

4 | $25,000 | =MATCH(A4, SalesData!$C$2:$C$4, 0) |

*Explanation:* In this example, we want to find the position of the specified Sales Amount in the list using the MATCH function. The formula in cell B2 would be “=MATCH(A2, SalesData!$C$2:$C$4, 0)”. The Function searches for the value “$32,000” (A2) within the Sales column (C2:C4) and returns the relative position, which is 2. If the Sales Amount is not found (A3 and A4), the Function will return the `#N/A`

error.

π **Example 4: Searching for Maximum Sales Amount (Sheet: “SalesData”)**

Table 1 (Sales Data Table) – Sheet: “SalesData”

A | B | C | |
---|---|---|---|

1 | Product ID | Product Name | Sales |

2 | 001 | Laptop | $25,000 |

3 | 002 | Smartphone | $32,000 |

4 | 003 | Tablet | $18,000 |

Table 2 (Maximum Sales Amount) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Maximum Sales Amount (with MATCH and MAX functions) | |

2 | =MAX(INDEX(SalesData!$C$2:$C$4, MATCH(MAX(SalesData!$C$2:$C$4), SalesData!$C$2:$C$4, 0))) |

*Explanation:* In this example, we want to find the maximum sales amount using the MATCH function nested with the MAX function. The formula in cell B2 would be “=MAX(INDEX(SalesData!$C$2:$C$4, MATCH(MAX(SalesData!$C$2:$C$4), SalesData!$C$2:$C$4, 0)))”. The inner MAX function finds the maximum sales amount from the Sales column (C2:C4), which is $32,000. The MATCH function then searches for this maximum Value within the Sales column and returns the relative position of 2. Finally, the INDEX function extracts the sales amount based on the matched position, giving us the maximum sales amount.

π **Example 5: Average Scores for a Specific Exam (Sheet: “Exams”)**

Table 1 (Exam Scores Table) – Sheet: “Exams”

A | B | C | |
---|---|---|---|

1 | Student ID | Exam Name | Score |

2 | 001 | Math | 85 |

3 | 001 | Science | 90 |

4 | 002 | Math | 78 |

5 | 002 | Science | 88 |

Table 2 (Average Scores for Math Exam) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Average Score for Math Exam (with MATCH and AVERAGE Function) | |

2 | =AVERAGE(INDEX(Exams!$C$2:$C$5, MATCH(“Math”, Exams!$B$2:$B$5, 0))) |

*Explanation:* In this example, we want to calculate the average score for the Math exam using the MATCH function nested with the AVERAGE Function. The formula in cell B2 would be “=AVERAGE(INDEX(Exams!$C$2:$C$5, MATCH(“Math”, Exams!$B$2:$B$5, 0)))”. The MATCH function searches for the value “Math” within the Exam Name column (B2:B5) and returns the relative position of 2. The INDEX function then extracts the scores based on the matched position, and the AVERAGE function calculates the average score for the Math exam.

π **Example 6: Employee Bonus Calculation (Sheet: “EmployeeData”)**

Table 1 (Employee Data Table) – Sheet: “EmployeeData”

A | B | C | |
---|---|---|---|

1 | Employee ID | Name | Sales |

2 | 101 | John | $25,000 |

3 | 102 | Mary | $32,000 |

4 | 103 | Bob | $18,000 |

Table 2 (Employee Bonus) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Employee Bonus (with MATCH, IF, and SUM functions) | |

2 | =SUM(IF(INDEX(EmployeeData!$C$2:$C$4, MATCH(A2, EmployeeData!$A$2:$A$4, 0))>30000, 0.1*INDEX(EmployeeData!$C$2:$C$4, MATCH(A2, EmployeeData!$A$2:$A$4, 0)), 0)) |

*Explanation:* In this example, we want to calculate the bonus for employees whose sales exceed $30,000 using the MATCH function nested with the IF and SUM functions. The formula in cell B2 would be “=SUM(IF(INDEX(EmployeeData!$C$2:$C$4, MATCH(A2, EmployeeData!$A$2:$A$4, 0))>30000, 0.1*INDEX(EmployeeData!$C$2:$C$4, MATCH(A2, EmployeeData!$A$2:$A$4, 0)), 0))”. The MATCH function searches for the Employee ID in column A and returns the relative position. The INDEX function extracts the sales amount based on the matched position. The IF function checks if the sales amount is greater than $30,000. If true, it calculates the bonus as 10% of the sales amount; otherwise, it returns 0. The SUM function then adds all the bonuses to get the total employee bonus.

π **Example 7: Searching for the First Occurrence (Sheet: “DataSheet”)**

Table 1 (Data Table) – Sheet: “DataSheet”

A | B | C | |
---|---|---|---|

1 | ID | Name | Age |

2 | 101 | John | 30 |

3 | 102 | Mary | 28 |

4 | 103 | Bob | 35 |

Table 2 (First Occurrence) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | First Occurrence of Age 30 (with MATCH and IF functions) | |

2 | =INDEX(DataSheet!$B$2:$B$4, MATCH(TRUE, DataSheet!$C$2:$C$4=30, 0)) |

*Explanation:* In this example, we want to find the name of the first person aged 30 using the MATCH function nested with the IF function. The formula in cell B2 would be “=INDEX(DataSheet!$B$2:$B$4, MATCH(TRUE, DataSheet!$C$2:$C$4=30, 0))”. The MATCH function checks for the first occurrence of “TRUE” within the expression “DataSheet!$C$2:$C$4=30” (which evaluates to TRUE for the age of 30) and returns the relative position, which is 1. The INDEX function then extracts the name based on the matched position, giving us “John.”

π **Example 8: Sum of Specific Products (Sheet: “ProductsData”)**

Table 1 (Products Data Table) – Sheet: “ProductsData”

A | B | C | |
---|---|---|---|

1 | Product ID | Product Name | Price |

2 | 001 | Laptop | $800 |

3 | 002 | Smartphone | $600 |

4 | 003 | Tablet | $400 |

Table 2 (Sum of Specific Products) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Total Price of Selected Products (with MATCH and SUMIF functions) | |

2 | =SUMIF(INDEX(ProductsData!$B$2:$B$4, MATCH({“Laptop”,”Tablet”}, ProductsData!$B$2:$B$4, 0)), “Laptop”, ProductsData!$C$2:$C$4) |

*Explanation:* In this example, we want to calculate the total price of specific products using the MATCH function nested with the SUMIF function. The formula in cell B2 would be “=SUMIF(INDEX(ProductsData!$B$2:$B$4, MATCH({“Laptop”,”Tablet”}, ProductsData!$B$2:$B$4, 0)), “Laptop”, ProductsData!$C$2:$C$4)”. The MATCH function searches for the array of product names ({“Laptop”,”Tablet”}) within the Product Name column (B2:B4) and returns an array of relative positions. The INDEX function then extracts the prices based on the matched positions. The SUMIF function then sums the prices for the selected products (“Laptop” in this case).

π **Example 9: Finding the Closest Value (Sheet: “DataSheet”)**

Table 1 (Data Table) – Sheet: “DataSheet”

A | B | |
---|---|---|

1 | ID | Value |

2 | 001 | 12 |

3 | 002 | 25 |

4 | 003 | 18 |

Table 2 (Closest Value) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Closest Value to 20 (with MATCH, ABS, and MIN functions) | |

2 | =INDEX(DataSheet!$B$2:$B$4, MATCH(MIN(ABS(DataSheet!$B$2:$B$4-20)), ABS(DataSheet!$B$2:$B$4-20), 0)) |

*Explanation:* In this example, we want to find the closest Value to 20 using the MATCH function nested with the ABS and MIN functions. The formula in cell B2 would be “=INDEX(DataSheet!$B$2:$B$4, MATCH(MIN(ABS(DataSheet!$B$2:$B$4-20)), ABS(DataSheet!$B$2:$B$4-20), 0))”. The ABS function calculates the absolute differences between each Value in columns B and 20. The MIN function finds the minimum absolute difference of 2 (|18-20|). The MATCH function then searches for this minimum Value within the calculated differences and returns the relative position of 3. Finally, the INDEX function extracts the Value based on the matched position, giving us the closest Value, 18.

π **Example 10: Grouping Data by Category (Sheet: “SalesData”)**

Table 1 (Sales Data Table) – Sheet: “SalesData”

A | B | C | |
---|---|---|---|

1 | Category | Product Name | Sales |

2 | Electronics | Laptop | $25,000 |

3 | Electronics | Smartphone | $32,000 |

4 | Fashion | Dress | $18,000 |

Table 2 (Total Sales by Category) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Total Sales by Category (with MATCH, SUMIFS, and INDEX functions) | |

2 | =SUMIFS(INDEX(SalesData!$C$2:$C$4, MATCH(“Electronics”, SalesData!$A$2:$A$4, 0)), SalesData!$A$2:$A$4, “Electronics”) |

*Explanation:* In this example, we want to calculate the total sales for the “Electronics” category using the MATCH function nested with the SUMIFS and INDEX functions. The formula in cell B2 would be “=SUMIFS(INDEX(SalesData!$C$2:$C$4, MATCH(“Electronics”, SalesData!$A$2:$A$4, 0)), SalesData!$A$2:$A$4, “Electronics”)”. The MATCH function searches for the value “Electronics” within the Category column (A2:A4) and returns the relative position 1. The INDEX function then extracts the sales amounts based on the matched position. The SUMIFS function sums the sales amounts for the “Electronics” category.

π **Example 11: Calculating Bonus Percentage (Sheet: “EmployeeData”)**

Table 1 (Employee Data Table) – Sheet: “EmployeeData”

A | B | C | |
---|---|---|---|

1 | Employee ID | Name | Sales |

2 | 101 | John | $25,000 |

3 | 102 | Mary | $32,000 |

4 | 103 | Bob | $18,000 |

Table 2 (Bonus Percentage) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Bonus Percentage (with MATCH and IF functions) | |

2 | =IFERROR(INDEX({0.05, 0.08, 0.1}, MATCH(INDEX(EmployeeData!$C$2:$C$4, MATCH(A2, EmployeeData!$A$2:$A$4, 0)), {“>=15000″,”>=30000″,””}, 0)), 0) |

*Explanation:* In this example, we want to calculate the bonus percentage based on sales using the MATCH function nested with the IFERROR, INDEX, and MATCH functions. The formula in cell B2 would be “=IFERROR(INDEX({0.05, 0.08, 0.1}, MATCH(INDEX(EmployeeData!$C$2:$C$4, MATCH(A2, EmployeeData!$A$2:$A$4, 0)), {“>=15000″,”>=30000″,””}, 0)), 0)”. The MATCH function searches for the sales amount of the employee based on their Employee ID. The nested MATCH function searches for the sales amount within the array {“>=15000″,”>=30000″,””} and returns the relative position, which corresponds to the bonus percentage. The INDEX function then extracts the bonus percentage based on the matched position. The IFERROR function handles the case when the sales amount is not found and returns 0 as the bonus percentage.

π **Example 12: Dividing Data into Bins (Sheet: “DataSheet”)**

Table 1 (Data Table) – Sheet: “DataSheet”

A | B | |
---|---|---|

1 | ID | Value |

2 | 001 | 12 |

3 | 002 | 25 |

4 | 003 | 18 |

Table 2 (Bins) – Sheet: “ResultsSheet”

A | B | |
---|---|---|

1 | Bins for Values (with MATCH, VLOOKUP, and INDEX functions) | |

2 | =INDEX({“Low”,”Medium”,”High”}, MATCH(VLOOKUP(DataSheet!$B$2:$B$4, {0,15,20,999}, 2), {0,15,20,999}, 1)) |

*Explanation:* In this example, we want to categorize values into bins (Low, Medium, High) using the MATCH function nested with the VLOOKUP and INDEX functions. The formula in cell B2 would be “=INDEX({“Low”,”Medium”,”High”}, MATCH(VLOOKUP(DataSheet!$B$2:$B$4, {0,15,20,999}, 2), {0,15,20,999}, 1))”. The VLOOKUP function searches for each Value in column B within the lookup array {0,15,20,999} and returns the corresponding bin value. The MATCH function then searches for this bin value within the array {0,15,20,999} and returns the relative position corresponding to the bin category. The INDEX function then extracts the bin category based on the matched part, giving each Value the appropriate bin category.

π Part 3: Tips and Tricks π

- Use the
`MATCH`

function in combination with other tasks like`INDEX`

,`VLOOKUP`

,`HLOOKUP`

, etc., to perform more complex lookups and data retrievals. - Always ensure that the
`lookup_array`

is sorted in the correct order (if using the optional`match_type`

) to get accurate results. - To handle the
`#N/A`

error when a match is not found, consider using the`IFERROR`

Function to display a custom message or Value.

π With the MATCH function in Excel, you can easily find the position of specific values within a range and use that information to perform various data manipulations and analyses. Mastering this Function will greatly enhance your data lookup and retrieval capabilities! π