Contents
Function IF is one of the most used in Excel. It allows you to perform only those actions of actions that match the criteria specified by the user. It can be used to test conditions of any type, including text conditions. Today we will analyze the use of this function for situations in which the criterion is compliance or non-compliance with a certain text.
First, let’s describe in general terms how to use the function IF. This is a conditional statement that allows you to check whether a particular value matches a particular criterion. It contains three required arguments:
- Condition. It can act as an arithmetic sign or a logical function. In any case, the main requirement for the condition is that it must return a value. TRUE or LYING.
- Value if TRUE. This is the text that is displayed or the function that will be executed if the value is true. Multiple nested functions allowed IF in order to perform multi-level checks.
- Value if LYING. This is a text, function, or value of any other type that will be displayed or executed if the criterion described in the first argument is not true.
Now, after we remembered the syntax of this function, let’s take a closer look at the features of its use along with text values.
Checking condition for complete text match (IF + EXACT)
Text match is one of the most commonly used conditions in Excel. When checking, you need to pay special attention to the case of the text, because it is not taken into account by the function IF. To check the case of text, you need to use completely different functions.
In general, to test whether a variable matches a text string, you must use the logical operator =. The general function looks like this: =IF(G2=”done”,TRUE,FALSE).
In this case, strings can be written in different case, and this factor will not be taken into account in the function IF. And what if you need to check for the difference in case of letters? To do this, instead of the = operator, you need to use the function EXACT, which checks if text strings are identical.
Don’t forget to enclose all text values in quotation marks.
The function itself works as follows: it looks at text string 1, text string 2. If they match exactly, then it passes TRUE to the cell or function argument. If at least one difference is found as a result of parsing the strings, the function returns FALSE.
To pass the EXACT function to a function IF, you must use it as the first argument. For example, the formula might look like this: =IF(EXACT(G2,”Done”),”Yes”,”No”). As we can see in this example, this formula understands that the strings “DONE” and “done” are not identical.
This function will be useful, for example, if you need to compare product SKUs or perform any other action that requires strings to be case-sensitive.
IF function with partial text match
Earlier, we figured out how strings are compared for a complete match. But in some situations, only a partial match of the string with the standard is enough. For example, we need a city, but which one is not so important. The easiest way to do this is to use the ? and *. But this is not a good idea in all situations.
IF + SEARCH
One of the most reliable ways to achieve your goal is to use the function SEARCH as function argument IF. With its help, the user can determine the serial number of the character that starts the line count. In general, this function looks like this: =SEARCH(what_we’re looking for, where_we’re looking for, starting_from_what_character_we’re_looking for).
However, the third argument is optional. If it is not specified, the search will be performed from the very beginning of the string. The return value in this function is the number of the character with which the desired line begins or the error code.
This function must be used in conjunction with another operator. It’s called ISNUMBER. Its task is to check whether a certain variable is a number. If yes, the value of TRUE is passed to the cell or function. It can be used to check if a particular variable is a text variable. Here’s how to use it for our purposes: =IF(ISNUMBER(SEARCH(“city”,B2)),”City”,””).
This function works according to the following algorithm: it checks if the value returned by the lookup function is numeric. If so, the return value is “City”. The logic is very simple. The fact is that this function also filters any other data types, except for text. Since the “Search” function can also return an error if the corresponding number is not found, the function ISNUMBER allows you to filter it. If it turns out that the function ISNUMBER says that an error was thrown, then an empty value is passed as an argument.
The only thing to keep in mind when using this function is that it is not suitable for case-sensitive discrimination of partial matches. That is, this method is essentially the same as using the = sign, only for a partial match.
IF + FIND
If the user needs to compare strings by case, then another function is used – TO FIND. It works in exactly the same way, only it takes into account capital and small letters. Its syntax includes the following arguments:
- What are we looking for.
- Where is the search performed.
- From what position the desired text is being searched.
Therefore, the use of the formula will be exactly the same. =IF(ISNUMBER(SEARCH(“city”,B2)),”City”,””). As we see, a function IF allows you to perform a wide variety of operations. This is one of the main operators in Excel, which allows you to automate almost everything that is possible.