How-To

How to Check for Partial Matches in Excel

By Adam Davidson

Updated November 9, 2023

Do you need to match part of a string in your spreadsheets? Learn how to check for partial matches in Excel in this guide.

When you’re working with data in a spreadsheet, you can quickly fill up a huge number of cells. Trawling through all of those cells to find specific text strings isn’t something you’d want to do manually. But this is Excel, right? Finding partial matches should be a piece of cake. Well, it turns out it’s not quite as simple as you might think. The functions that immediately spring to mind when you’re thinking about searching for text don’t always play that well with the wildcards that you’ll need to use in order to search for a partial match.

Thankfully, there are ways to get around this. In fact, there are several ways. Here’s how to check for partial matches in Excel.

How to Check for Partial Matches in Excel Using IF

You would think that searching for partial matches in Excel would be a simple case of using the IF function with some wildcards. Unfortunately, things aren’t quite that simple. For some reason, Excel really doesn’t like having wildcards used directly after an equals sign in a logical statement, such as you find in an IF function.

You can get around this, however, by combining IF with other functions where wildcards do work.

To check for partial matches in Excel using IF:

1. Select the first cell where you want to check for a partial match.
2. Enter the following formula, replacing B4 with the cell that you want to check for a partial match and *the* with the text you want to search for between two asterisks:

   =IF(COUNTIF(B4,"*the*"),"Partial Text Found","No Match")

   

3. Press Enter, and the formula will check for a partial match for the string ‘the’.
   
4. To apply the formula to other cells, click and hold the drag handle in the bottom corner of the cell.
   
5. Drag down over the cells where you want the formula to appear.
   
6. Release your mouse or trackpad, and the formula is now applied to all of your selected cells.
   
7. You can change “Partial Text Found” and “No Match” to whatever you wish.

How to Check for Partial Matches in Excel Using MATCH

You can also use the MATCH formula to search for partial matches in Excel. The MATCH formula returns the position of the first match found in your given range. For example, if the text is found in the third cell of your range, the MATCH formula would return 3.

You can then combine this with the INDEX function to return the string that contains the partial match.

To check for partial matches using MATCH:

1. In an empty cell, enter the partial text you want to search for.
   
2. In the next cell, enter the following formula, replacing C4 with the cell containing your partial text, and B4:B11 with the range of cells you want to search:

   =MATCH("*"&C4&"*",B4:B11,0)

   

3. Press Enter and the formula will return the position of the first cell containing the requested partial text.
4. To turn the position of the partial match into the string containing that text, change the formula to the following:

   =INDEX(B4:B11,MATCH("*"&C4&"*",B4:B11,0))

   

5. Press Enter and the formula will calculate.
   
6. You can edit the cell containing the partial text string you want to search for, and it will automatically be applied to your formula.
   

How to Check for Partial Matches Using SEARCH

Another way to check for partial matches in Excel is to use the SEARCH function. This function returns the position of the text you are searching for within the string itself. So, for example, if the partial text was found at the very start of the string, it would return 1. If it was found starting from the fourth character, it would return 4. If the partial text is not found at all, the SEARCH function returns the #VALUE! error.

Since the function returns a number when the text is found, and an error when it is not, we can use the ISNUMBER function to determine whether the function was found or not. Combine that with the IF function, and we can generate our own text when the partial text is, or is not, found.

To check for partial matches using SEARCH:

1. Select an empty cell to enter your formula.
2. Enter the following formula, replacing B4 with the location of the first cell you want to search, and *ou* with the partial text you want to search for:

   =IF(ISNUMBER(SEARCH("ou",B4)),"Match","No Match")

   

3. Press Enter and the formula will calculate.
   
4. To apply it to other cells, click and hold the drag handle and the bottom of the cell.
   
5. Drag down over the cells where you want the formula to appear.
   
6. Let go, and the cells will fill with the formula, and calculate.
   

How to Check for Partial Matches Using VLOOKUP

If you want to check for a partial match in Excel but need to return data from somewhere else in the same row rather than the text itself, then you can use the VLOOKUP function. A similar method can be used with the HLOOKUP and XLOOKUP functions, too.

This method looks through a column of data for the partial text you are looking for. If it finds it, it will return the value from any other cell in that row that you specify. This can be useful for quickly searching through tables for test scores or other numerical data.

To check for partial matches using VLOOKUP:

1. Enter the partial text you want to search for in an empty cell.
   
2. In another cell, enter the following formula, replacing F4 with the cell containing your search text, $B$4:$D$11 with the cells containing your data, and 2 with the number of the column that you want to return the value from:

   =VLOOKUP("*"&$F$4&"*",$B$4:$D$11,2,FALSE)

   

3. Press Enter and the formula will calculate.
   
4. You can use the same formula with a different column value to pull other data from the same row.
5. Changing the cell containing your search string will automatically look for this new partial text and return updated results for your formulas.
   

Finding Results in Excel

Finding partial matches in Excel isn’t as easy as you might expect. Once you know how to check for partial matches, however, you can apply your knowledge in a wide range of different instances.

There are plenty of other useful Excel tips and tricks you can learn next. If you want to distinguish between zero and non-zero values in your data quickly, you can learn how to convert a zero to a dash in Excel. If you’re searching for cells containing text, you can quickly count up all cells containing text in your spreadsheet. Or perhaps one of my favorites pulling data from one sheet to another in Excel.

Excel is one of Microsoft’s most useful applications, and many take it for granted. It can be used for almost any task, and even issues with Excel formulas can be solved once you understand the basics. Do you have a favorite function? We love hearing from our readers so please drop us a comment.