Excel is a powerful tool for data analysis and manipulation, and mastering partial matches in Excel can greatly enhance your ability to work with complex datasets. Partial matching allows you to find specific patterns or substrings within text strings, opening up a world of possibilities for data extraction, filtering, and analysis. This comprehensive guide will walk you through the procedures to check Partial Match in Excel.
Download Practice Workbook
You can download the practice workbook here and exercise.
How to Find Partial Match in Excel
In this section, we will discuss some methods to find partial matches in Excel. Let’s use some of the functions and features of Excel.
1. Using the ISNUMBER Function
In the first method, we will use the ISNUMBER function along with the IF and OR functions. The used formula will check the letters from Match String 1 and Match String 2 in column Name, if it finds, will return Yas otherwise No.
- Firstly, apply the following formula to a cell. Press Enter.
- Then, use the Fill Handle to copy the formula into the following cells.
=IF(OR(ISNUMBER(SEARCH(C5,B5)),ISNUMBER(SEARCH(D5,B5))),"YES","NO")
2. Applying the SEARCH Function
In the first method, we will use the SEARCH function along with the IF and the ISNUMBER functions to get our partial math detected. The used formula will check the letters from Match String 1 and Match String 2 in column Name, if it finds, will return Yas otherwise Not found.
- Firstly, apply the following formula to a cell. Press Enter.
- Then, use the Fill Handle to copy the formula into the following cells.
=IF(ISNUMBER(SEARCH(C5,B5)),"YES","NOT FOUND")
3. Use of the VLOOKUP Function
In the first method, we will use the VLOOKUP function. If VLOOKUP finds the Name, it will deliver the rank.
- Firstly, apply the following formula to a cell. Press Enter.
- Also, use the Fill Handle to copy the formula into the following cells.
=VLOOKUP($E$5&"*",$B$5:$C$10,2,FALSE)
4. Inserting XLOOKUP Function
In the first method, we will use the XLOOKUP function. It works the same as VLOOKUP, except it works horizontally.
- Firstly, apply the following formula to a cell. Press Enter.
- Then, use the Fill Handle to copy the formula into the following cells.
=XLOOKUP(TRUE,ISNUMBER(SEARCH($B$5:$B$10,E5)),$C$5:$C$10)
5. Combining INDEX and MATCH Functions
In the first method, we will use the combination of the INDEX and MATCH functions. It works similarly to the VLOOKUP function.
- Firstly, apply the following formula to a cell. Press Enter.
- In addition, use the Fill Handle to copy the formula into the following cells.
=INDEX($B$5:$B$10,MATCH(E5&"*",$B$5:$B$10,0))
6. Combining Multiple Functions to Find Partial Match in Two Columns
In this method, we will use the ISNUMBER function along with the IF, AND, and SEARCH functions. So, their combination can be used to get a partial match. If it finds the match then, it will show Found.
- Firstly, apply the following formula to a cell. Press Enter.
- Then, use the Fill Handle to copy the formula into the following cells.
=IF(AND(ISNUMBER(SEARCH($E$6, B5)), ISNUMBER(SEARCH($F$6, B5))), "Found", "")
7. Applying Array Formula to Find Partial Match
In this method, we will use the SEARCH function along with the IF and COUNT functions. If the formula finds A in the name data of Name with the ID column then, it will give the output “Found”.
- Firstly, apply the following formula to a cell. Press Enter.
- Then, use the Fill Handle to copy the formula into the following cells.
=IF(COUNT(SEARCH({"A","12"}, B5))=2, "Found", "")
How to Match Partial Numbers in Excel
So far, we have checked for a partial match of string type. Of course, we can do a similar task for numbers as well. Let’s check out how to do that.
- Firstly, apply the following formula to a cell. Press Enter.
- In addition, use the Fill Handle to copy the formula into the following cells.
=IF(ISNUMBER(SEARCH("*"&E5,B5)),"Matched","Not Matched")
How to Match Partial Duplicates in Excel
Also, why not check for the presence of partial duplicates in our dataset! We will simply use the COUNTIF function to count the number of duplicates in column Formula 1.
- Firstly, apply the following formula to a cell. Press Enter.
- Afterward, use the Fill Handle to copy the formula into the following cells.
=COUNTIF(C:C,C5)
How to Find the Partial Match Row Position in Excel
In addition, we will use the MATCH function along with the “&” logic to get the row number where two columns of data partially match.
- Firstly, apply the following formula to a cell. Press Enter.
- Again, copy the formula into the following cells using the Fill Handle.
=MATCH("*"&D6&"*", B5:B10, 0)
Things to Remember
- Please keep in mind, if the VLOOKUP function can’t find the lookup value in the lookup table, it’ll return an error.
- Also, if the XLOOKUP function can’t find the lookup value in the lookup table, it’ll return an error.
- Sometimes, we used absolute cell references with the “$” sign to copy them without formula adaptation.
- Also, remember Excel is not case-sensitive by default, so the methods we have shown count all the partial matches despite their case criteria.
Conclusion
Partial match in Excel is a valuable skill that empowers users to efficiently search, extract, and analyze data based on specific patterns or substrings. By utilizing functions like VLOOKUP, INDEX/MATCH, COUNTIF, and SUMIF, users can perform powerful partial matching operations. Additionally, we have shown the ways to find partial duplicates, partial match positions, etc. Kindly, leave comments if you have any.
Frequently Asked Questions
1. What is a partial match in Excel?
Ans: In Excel, a partial match refers to finding a substring or partial text within a larger text string or number. Also, It allows you to search for a specific pattern or set of characters within a cell or range of cells.
2. Can I perform a partial match with multiple criteria in Excel?
Ans: Yes, you can perform a partial match with multiple criteria in Excel by using array formulas or advanced functions like ISNUMBER, SEARCH, etc. Also, these functions allow you to specify multiple criteria and perform partial matching for each criterion.
3. Can I perform a partial match with numbers or numeric values in Excel?
Ans: Yes, you can perform a partial match with numbers or numeric values in Excel. The same partial matching functions like VLOOKUP, INDEX/MATCH, COUNTIF, and SUMIF can be used with numbers. Excel will consider numeric values as text when performing partial matching operations. However, it’s important to ensure that the number formatting and data types are consistent to avoid unexpected results.
Partial Match Excel: Knowledge Hub
<< Go Back to Formula List | Learn Excel
