# MATCH Function in Excel

π 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:

syntax
`=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”

ABC
1Employee IDNameDepartment
2101JohnSales
3102MaryMarketing
4103BobFinance

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

AB
1Search ValuePosition of Employee ID in the List
2102=MATCH(A2, EmployeeData!\$A\$2:\$A\$4, 0)
3104=MATCH(A3, EmployeeData!\$A\$2:\$A\$4, 0)
4101=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”

ABC
1Product IDProduct NamePrice
2001Laptop\$800
3002Smartphone\$600
4003Tablet\$400

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

AB
1Search ValuePosition of Product Name in the List
2Smartphone=MATCH(A2, Products!\$B\$2:\$B\$4, 0)
4Laptop=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”

ABC
1Product IDProduct NameSales
2001Laptop\$25,000
3002Smartphone\$32,000
4003Tablet\$18,000

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

AB
1Search ValuePosition 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”

ABC
1Product IDProduct NameSales
2001Laptop\$25,000
3002Smartphone\$32,000
4003Tablet\$18,000

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

AB
1Maximum 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”

ABC
1Student IDExam NameScore
2001Math85
3001Science90
4002Math78
5002Science88

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

AB
1Average 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”

ABC
1Employee IDNameSales
2101John\$25,000
3102Mary\$32,000
4103Bob\$18,000

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

AB
1Employee 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”

ABC
1IDNameAge
2101John30
3102Mary28
4103Bob35

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

AB
1First 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”

ABC
1Product IDProduct NamePrice
2001Laptop\$800
3002Smartphone\$600
4003Tablet\$400

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

AB
1Total 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”

AB
1IDValue
200112
300225
400318

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

AB
1Closest 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”

ABC
1CategoryProduct NameSales
2ElectronicsLaptop\$25,000
3ElectronicsSmartphone\$32,000
4FashionDress\$18,000

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

AB
1Total 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”

ABC
1Employee IDNameSales
2101John\$25,000
3102Mary\$32,000
4103Bob\$18,000

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

AB
1Bonus 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”

AB
1IDValue
200112
300225
400318

Table 2 (Bins) – Sheet: “ResultsSheet”

AB
1Bins 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! π