How to Perform Excel Match? (8 Different Cases)

This article is about how to match in Excel. In this Excel tutorial, you will learn
– The basics of the MATCH function
– How to find exact and approximate matches
– Case-sensitive and partial matches with wildcards
– The use of the MATCH function with other searching functions.
– Identifying matches and performing tasks based on matches using VBA

We have used Microsoft 365 to make the dataset here. These methods are also applicable to any version of Excel.

Finding matches is an integral part of data analysis. From searching out relevant information from a large dataset to finding errors by matching values, the possibilities of the MATCH function are endless. We can use it in conditional formatting too for matching specific criteria. Also, we can use it with other functions like INDEX or VLOOKUP to create useful combinations.

Download Practice Workbook

What is MATCH Function in Excel?

When working with Excel, you might need to find a specific value in a sea of data. That’s where the MATCH function comes in handy. It allows you to locate a value within a range of cells and returns its position.

Syntax:

• lookup_value: The value you want to find.
• lookup_array: The range of cells to search in.
• match_type: The type of match to perform (0 for exact match, 1 for less than or equal to, -1 for greater than or equal to).

Example: =MATCH(“John”, A1:A5, 0) This formula searches for “John” in cells A1 to A5 and returns the position of the value if found.

Related Functions of Excel Match:

Click on the functions individually to learn more about various match functions in Excel.

INDEX, VLOOKUP, HLOOKUP

How to Find Match in Excel?

There are several ways to find a match in Excel. You can find exact or approximate matches from a dataset by using the MATCH function. You can also use wildcard match, or you can particularly use the MATCH function with INDEX, VLOOKUP, or HLOOKUP to search for particular values in the dataset. And there is also a thing where you can compare two columns in the dataset.

1. Find Exact Match

We need to use zero as the third argument to find the exact match using the MATCH function.

To find the column index number of “Lenovo”, make sure to follow the steps.

• Select cell G5 and use the formula.

• Press Enter. 

It will return position 2 as the relative position of “Lenovo” from the “Brand” Column.

2. Find Approximate Match

To find an approximate match for a value that is not equal to Look Up Value but close to it. We need to use one as the third argument. These are the steps, you can follow.

• Before starting, make sure to arrange the columns in ascending order otherwise this approximate match might not work.
• In cell G5, type the formula below and press Enter.

This will return the position of the data.

3. Use Wildcard Match to Search with Partial Value

You can use a wildcard in the formula to match against the dataset. a wildcard is a tool that uses symbols like * or ? to help you search for and work with words or data flexibly. You should use these symbols for wildcard matches.

For instance, it can help you find words that partly match a certain pattern. You can search Lenovo in the dataset by just using Len* as a lookup value. Here are the steps that you can follow.

• Use the formula mentioned below in cell G5 and make sure to press Enter.

This should return the relative position 4 in the “Brand” Column.

4. Use INDEX and MATCH Functions Together to Find a Value Based on a Condition

MATCH will return the relative position of the data and INDEX will return the corresponding value. In this example, we are going to find particular values using INDEX and MATCH functions.

• Select G5 and use the formula mentioned below.

• Press Enter.

The price of the “Mobile Phone” option should come in cell G6 as shown in the figure.

5. Perform a Case-sensitive Match

To find a case-sensitive match in Excel, you have to use the EXACT function along with the MATCH function. It will find the exact match. You can follow the steps and formula below.

• In cell G5, type the formula mentioned below and press Enter.

It will return the column index number of “Lenovo” but not “LENOVO”.

Read More: Match Names in Excel Where Spelling Differ

6. Compare Two Columns for Matches and Differences

This should take a combination of IF, ISNA, and EXACT functions.

Here, we will find matching values in two columns and compare between them. If values of Product List-2 are present in Product List-1, it will return Bank otherwise “Not in List 1”. Here is how to do this.

• Select cell D5 and type the formula for Case Insensitive case.

• Press Enter.

Similarly, for case-sensitive issues, use the formula in cell E5.

Read More: Return the Row Number of a Cell Match in Excel

7. Use VLOOKUP and MATCH to Find Specific Value

The MATCH function finds the matching position, and we can use this as an argument for the input of the VLOOKUP function.

In this example, we will use a combination of VLOOKUP and MATCH functions to find a specific value, where MATCH will return the column index number and VLOOKUP will find the value against it. Follow the steps below.

• Select cell G5, and write down the formula as follows.

• Press the Enter button.

This will return the Price of “Smart Watch” which is 654.

Read More: If One Cell Equals Another Then Return Another Cell in Excel

8. Combine HLOOKUP and MATCH Functions to Search for Particular Value

• In cell C9, type the formula and press Enter.

It will return the Price of the Tablet from the dataset.

Which Things Should You Remember?

• The MATCH function in Excel is designed to return the position of the first matching value found within the specified range. If the range contains multiple occurrences of the lookup value, the MATCH function will only return the position of the first match it encounters.
• For the approximate match, the range must be sorted in ascending order.
• Use error handling functions like IFERROR or ISNA to manage errors.
• Ensure the range contains the desired values.
• Use 0 for exact match and 1 for approximate match for the MATCH function.

Frequently Asked Questions

1. How do you use Match in Excel?

To use the MATCH function in Excel, designate the result cell, input “=MATCH(” in it, and provide the value you’re seeking and the search range. You can include a third argument for match type (0, 1, or -1). Conclude the formula with “)” and press Enter to find the value’s position within the range.

For example, if you want to find the position of the value “5” in the range A1:A10, you would enter “=MATCH (5, A1:A10, 0)“.

2. What is INDEX MATCH in Excel?

INDEX MATCH is a powerful combination of Excel functions. It allows you to find a value in one column (MATCH) and retrieve a corresponding value from another column (INDEX). It’s more flexible than VLOOKUP and HLOOKUP, making it ideal for complex lookups and large datasets.

3. What is the exact match command in Excel?

The “exact match” command in Excel means using the option that ensures the lookup value matches exactly with the data being searched. It avoids any partial or approximate matches, providing precise results.

Conclusion

This article offers practical solutions for data matching in Excel. Also, the article highlights the power of Excel’s MATCH function, enabling users to efficiently search for data in each range.

If you have any confusion or questions regarding the article’s content on the Excel MATCH function, please feel free to leave a comment. I’ll be happy to assist you further and provide clarification on any aspects you may find unclear.

Excel Match: Knowledge Hub

<< Go Back to | Learn Excel