The MATCH function in Excel helps you find the position of a specific value within a range of cells. Instead of manually searching through rows or columns, MATCH delivers the relative location of your target data, saving you time and effort, especially when working with large datasets.
Syntax
The MATCH function uses the following syntax:
=MATCH(lookup_value, lookup_array, [match_type])
Arguments:
-
lookup_value: This required argument is the value you want to find. It can be a number, text, logical value, or a cell reference. -
lookup_array: This required argument is the range of cells to search within. It must be a one-dimensional array (either a single row or a single column). For certainmatch_typeoptions to work correctly, this array needs to be sorted. -
match_type: This optional argument specifies the type of match. It accepts -1, 0, or 1. If omitted, the default is 1.- 1: Finds the largest value less than or equal to the
lookup_value. Thelookup_arraymust be sorted in ascending order. - 0: Finds an exact match.
- -1: Finds the smallest value greater than or equal to the
lookup_value. Thelookup_arraymust be sorted in descending order.
- 1: Finds the largest value less than or equal to the
Things to Know
-
The
MATCHfunction is not case-sensitive. -
If the
MATCHfunction can’t find thelookup_value, it returns a#N/Aerror. -
MATCHworks with text strings up to 255 characters. -
If the
lookup_valueappears multiple times, theMATCHfunction returns the position of the first match. -
When
match_typeis 0 and thelookup_valueis a text string, you can use wildcard characters:?(question mark): Represents any single character.*(asterisk): Represents any sequence of characters.- To search for an actual
?or*, precede it with a tilde (~).
Examples of the MATCH Function
Here are several examples of how you can use the MATCH function in Excel.
Method 1: Combining INDEX and MATCH Functions
This is the most flexible and robust method.
Step 1: Start with a dataset in Excel. Suppose you have employee data with columns for ‘Employee ID’, ‘Name’, ‘Department’, and ‘Salary’. You want to retrieve an employee’s salary based on their name entered in cell G1.
Step 2: Use the INDEX and MATCH functions together. The MATCH function finds the row number corresponding to the employee’s name in the ‘Name’ column. The INDEX function then uses this row number to return the corresponding value from the ‘Salary’ column.
=INDEX(D2:D11,MATCH(G1,B2:B11,0))
In this formula:
D2:D11is the range containing the salaries.G1is the cell where the employee’s name is entered.B2:B11is the range containing the names of the employees.0specifies an exact match.
This method avoids the limitations of VLOOKUP by directly specifying the lookup and return ranges.
Method 2: Using VLOOKUP with MATCH Function
This is an improvement over the standard VLOOKUP formula.
Step 1: Suppose you work in the HR department, and you have an Excel dataset containing information about employees, including their names, departments, and salaries. Now, you want to retrieve an employee’s salary based on their name.
Step 2: The easiest and the most preferred option is to use the VLOOKUP function. The formula used will be as follows.
=VLOOKUP(G1,$A$2:$D$11,3,FALSE)
Step 3: Now, due to some reasons, you had to delete Column B containing the details about ‘Department’. Upon deleting the column, the above formula adjusted the value of the table_array but returned the value from the third column, which is ‘Joining Date’.
Step 4: To avoid this issue, we can use the MATCH function to dynamically determine the column index for the VLOOKUP function.
For instance, in this example, we are looking for values in the column named ‘Salary (USD)’. Using the MATCH function, we can dynamically determine the column index by finding the position of “Salary (USD)” in the first row (row 1) of the data. This ensures that if we add or remove columns, the formula will still work correctly. To find the column number, the formula used will be as follows.
=MATCH("Salary (USD)",A1:D1,0)
Step 5: Now, we can use this in the above-mentioned VLOOKUP function. The formula will be as follows.
=VLOOKUP(G1, $A$1:$D$1, MATCH("Salary (USD)",A1:D1,0), FALSE)
Now, even if we add or delete columns in the data table, the formula will still correctly retrieve the salary because it uses the MATCH function to determine the column index dynamically based on the column header ‘Salary (USD)’. The same logic can be applied while using the HLOOKUP function.
Method 3: Simple Use of MATCH Function
Step 1: Suppose you own a furniture store that sells various products such as bookcases, sofas, etc. You now have the dataset which contains details such as product IDs, product names, and total sales for each product to date.
Step 2: You now wish to determine the ranking of the product based on their sales using the product IDs.
We can take cell E1 where the user can enter the product ID to look for. We can then use the MATCH function to determine the position or row number of the product ID. As we are looking for an exact match, the formula used will be as follows.
=MATCH(E1,A2:A11,0)
This is extremely useful when the dataset is large. As the current dataset is sorted from highest sales to lowest, the result obtained from the MATCH function also provides us with the product’s ranking or popularity based on sales.
Step 3: Furthermore, it is evident that the product IDs are crafted in a way where the initial three letters of the product ID align with the initial three letters of the product name. In case the user does not remember the entire product ID, we can take advantage of the wildcard characters supported by the MATCH function. Next, we’ll show you how to do that.
Method 4: Using Wildcard with MATCH Function
Step 1: To simplify the user experience, we can ask them to just enter the initial three letters of the product name. Subsequently, we modify the lookup_value argument, where we combine the user-entered first three letters of the product with the wildcard character *. This means that the MATCH function will look for a value that begins with the user-entered text followed by any number of characters. The formula will be as follows.
=MATCH(E1&"*",A2:A11,0)
Step 2: The asterisk has served as a buffer for the remaining characters of the product code “SOF234PQR” beyond the characters entered in E1. As observed in the previous example and stated in the characteristics, the MATCH function is case-insensitive. In the next example, let’s explore the possibility of using the MATCH function in a case-sensitive manner.
Method 5: Making MATCH Function Case-sensitive
Step 1: Suppose you are a marketing manager planning a fresh marketing campaign for your company, and you wish to collaborate with social media influencers to promote the campaign. You have a dataset that includes details of potential influencers, such as their name, username, and the number of followers they have on social media.
Step 2: To make it easy to filter out, you now wish to identify the position of the influencer based on their username.
Step 3: As we can see, the usernames contain both uppercase and lowercase letters, making it clear that relying solely on the MATCH function would be insufficient. Instead, we will use a combination of the EXACT function with the MATCH function.
The EXACT function will search for an exact case-sensitive match of the lookup_value. If it finds a match, the EXACT function returns TRUE, else it returns FALSE. The formula used will be as follows.
=EXACT(E1,B2:B15)
Step 4: The EXACT function returned an array of TRUE and FALSE indicating where it found a perfect case-sensitive match. Now, we can use the MATCH function to look for “TRUE” in the previous array. The formula used will be as follows.
=MATCH(TRUE,EXACT(E1,B2:B15),0)
So, together, the EXACT function helps us figure out if there is a perfect match, and the MATCH function helps us find where that perfect match is in a list.
Method 6: Comparing Two Lists Using MATCH Function
Step 1: In this scenario, we have an upcoming technical training program that is open to all teams in the company. We now have two lists of email addresses, the first list contains the email addresses of the technical team, and List 2 contains a list of email addresses of employees who have been invited to a special training program.
Step 2: The goal is to determine which email addresses from the training program list are a part of the tech team and which ones are not.
First, the MATCH function compares each cell in column B with all the data in column A. The formula used is as follows.
=MATCH(B2,A:A,0)
Step 3: We drag and drop the formula till the end of the dataset.
The next step is to check if the return value of the MATCH function is an error (specifically, a #N/A error). If there is an error, the ISNA function returns TRUE; otherwise, it returns FALSE. The formula will be as follows.
=ISNA(MATCH(B2,A:A,0))
Step 4: The last step is the simplest where we enclose the above formula in a basic IF function. It checks the return value of the ISNA and MATCH functions. If the value is TRUE, meaning the email address in column B was not found in column A), it displays “Not in Tech Team”; otherwise, it displays “Member of the Tech Team.” The complete formula will be as follows.
=IF(ISNA(MATCH(B2,A:A,0)), "Not in Tech Team", "Member of the Tech Team")
In case, the columns to be compared contain case-sensitive values, combine the logic mentioned in the previous example to improve this formula.
By mastering the MATCH function, you’ll be able to efficiently locate data within your Excel worksheets and create more dynamic and robust spreadsheets. Keep practicing and exploring, and you’ll discover even more ways to use this powerful tool in your daily work.