MATCH Function in Excel

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

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

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

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”

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

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”

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

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”

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

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”

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

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”

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

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”

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

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”

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

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”

Table 2 (Bins) – Sheet: “ResultsSheet”

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 🌟