Hey besties! Have you ever tried to find a partial match in the IF function in Google Sheets, only to realize that wildcard characters just won’t cut it? Well, don’t worry, because I’ve got some juicy secrets to share with you today. In this tutorial, I’ll show you how to use the IF function to find a partial match in Google Sheets, along with some additional tips that you won’t want to miss. So grab your favorite beverage and let’s dive right in!
Finding a Partial Match in the IF Function
Let’s start with a basic example. Suppose you have a value in cell A1 and you want to check if it matches a certain condition using the IF function. Normally, you would write a formula like this:
=IF(A1="Gr. 0001.55", CHAR(10004))This formula would return a tick mark (✔) if the value in cell A1 matches the condition. But what if you want to find a partial match in cell A1? Say, for example, you want the formula to return a tick mark even if the value in A1 is “Gr.0001.55”.
Formula 1: The Wildcard Alternative
Unfortunately, wildcard characters won’t work in this case. But fret not, my friend! I have not one, but two formulas that use the FIND function to achieve a partial match.
Formula 2 (Partial Match in IF):
=IFERROR(IF(FIND("Gr. 0001",A1)>=1,CHAR(10004)),FALSE)Formula 3 (Partial Match in IF):
=IF(COUNT(FIND("Gr. 0001",A1))>=1,CHAR(10004))In both of these formulas, the FIND function plays a key role. It returns the following:
- Returns 1 if the search keyword (“Gr. 0001”) is found at the beginning of the text in cell A1.
- Returns a number greater than 1 if the search keyword is not at the beginning.
- Returns an error value (#VALUE!) if there is no partial match.
In the first formula, we use IFERROR to handle the case where there is no partial match. It returns FALSE instead of the error value. In the second formula, we use the COUNT function to check if the output of FIND is greater than or equal to 1. If it is, we return the tick mark. Otherwise, the formula returns nothing.
Partial Match in IF, OR Logical Functions
Now that we’ve covered partial match in the IF function, let’s take it up a notch and explore how to use partial match in combination with the OR logical function.
Formula 4:
=ArrayFormula(IF(COUNT(FIND("Gr.0001",A1:C1))>=1,CHAR(10004)))In this formula, we use the OR logical function along with the FIND function to return a tick mark if any of the values in cell A1, B1, or C1 match “Gr.0001”. This formula is similar to the previous one (Formula 3) but takes into account multiple cells or a range. To make the formula work correctly, we use the ArrayFormula function.
Partial Match in IF, AND Logical Functions
Lastly, let’s explore how to use partial match in combination with the AND logical function.
Formula 5:
=IF(COUNT(ArrayFormula(FIND("Gr.0001",A1:C1)))=COLUMNS(A1:C1),CHAR(10004))In this formula, we use the AND logical function to return a tick mark only if all the values in cell A1, B1, and C1 have a partial match. The only difference here is that instead of using “>=1”, we use the COLUMNS formula. Alternatively, you can directly use the number 3 since the COLUMNS formula would return 3 in this case.
Well, my dear friends, that’s all there is to it! Now you know how to use partial match in the IF function, as well as in combination with the OR and AND logical functions, in Google Sheets. Say goodbye to wildcard frustrations and embrace these nifty formulas. Your spreadsheets will thank you!
Enjoy your newfound knowledge and happy spreadsheeting! And if you want to learn more amazing tips and tricks, don’t forget to visit Crawlan.com for more exciting content.
Additional Resources:
- Formula to Find Partial Match in Two Columns in Google Sheets.
- Partial Match in Vlookup in Google Sheets [Text, Numeric and Date].
- CONTAINS Substring Match in Google Sheets Query for Partial Match.
- Highlight Partial Matching Duplicates in Google Sheets.
- Lookup Last Partial Occurrence in a List in Google Sheets.
