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”

	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! 🌟

Post Views: 22