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:
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:
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:
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:
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 in Excel, however, then 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 quickly distinguish between zero and non-zero values in your data, 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. And if your complex formulas are producing errors, you can learn how to fix problems with Excel formula references. Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
