To demonstrate how to use IF-THEN statements with text in Excel, we will use the following dataset, which contains columns for Product, Color, and Size:
Method 1 – Combining IF, SEARCH, and ISNUMBER Functions to Find a Certain Text String
Suppose you want to know how many products such as shirts or t-shirts are in our dataset. The text “Shirt” is common to both products, so we apply the formula based on the text “Shirt”.
STEPS:
- Enter the following formula in cell E5:
=IF(ISNUMBER(SEARCH("shirt", B5)), "Found", "Not Found")- Press ENTER.
Formula Breakdown
- The SEARCH function will search for the value “shirt” and return the position of this text within cell B5.
- So, SEARCH(“shirt”, B5)—> returns 1.
- The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. Here it checks whether the SEARCH function’s result is a number or not.
- Thus, ISNUMBER(1)—> returns TRUE.
- The IF function will return a logical test result. Here, “Found” —> when the logical test is TRUE it will return “Found“. Basically, an Inverted Comma is a must for getting a text as the output. “Not Found” —> denotes that when the logic fails it will return “Not Found“.
- Drag the Fill Handle down to copy the formula to the other cells of the column.
The number of products that are shirts or t-shirts is returned.
Method 2 – Merging IF, and EXACT Functions to Match a Text String
Suppose you want to know how many products are Shirts.
Steps:
- Enter the following formula in cell E5.
=IF(EXACT("Shirt", B5), "Found", "Not Found")- Press ENTER.
Formula Breakdown
- The EXACT function will check whether the cell value of B5 is as same as “Shirt”.
- The IF function will return a logical test result. If the EXACT function returns TRUE then the IF function will return “Found”. Otherwise, the IF function will return “Not Found“.
- Drag the Fill Handle icon to copy the formula to the other cells of the column.
The number of products that are Shirts is returned.
Method 3 Using IF Function to Get Status of Availability Only
Suppose you want to know how many products are Shirts only.
Steps:
- Enter the following formula in cell E5:
=IF(B5="shirt", "Found", "Not Found")- Press ENTER.
Formula Breakdown
Here, the IF function will return the output of a logical test.
- B5=”shirt” denotes the logical test, which will check whether the cell value of B5 is equal to shirt or not.
- “Found” —> when the cell value of B5 is equal to shirt then it will return Found. Here, an Inverted Comma is a must for getting a text as the output.
- “Not Found” —> when the logic fails then it will return Not Found.
- Drag the Fill Handle icon to copy the formula to the other cells of the column.
As a result, you will know how many products contain shirts.
Method 4 – Combining IF and ISTEXT Functions
Suppose you want to check whether the values in the Size column are correct. If the Size column contains a numerical value, then it will not be correct as the value should be alphabetic like XL, XXL, S, M, L, or XS. We will use the ISTEXT function to perform this task.
Steps:
- Enter the following formula in cell E5:
=IF(ISTEXT(D5),"OK","Wrong Info")- Press ENTER.
Formula Breakdown
- Here, the ISTEXT function will check whether the cell value of B5 is text or not.
- The IF function will return “Found” when the ISTEXT function returns TRUE, else the IF function will return “Not Found“.
- Drag the Fill Handle icon to copy the formula to the other cells of the column.
Wrong Info will be returned as the status if the size of any product is not correct.
Method 5 – Combining OR Function with IF-THEN Statements to Find a Certain Text in Excel
Suppose you want to know if there is any products which is a shirt or has any shade of the color green.
Steps:
- Enter the following formula in cell E5:
=IF(OR(B5="shirt",ISNUMBER(SEARCH("green",C5))),"Found", "Not Found")- Press ENTER.
Formula Breakdown
- The OR function will consider two logical tests. One is B5=”shirt” and other is ISNUMBER(SEARCH(“green”,C5)).
- B5=”shirt” will check whether the cell value of B5 is equal to shirt or not.
- B5=”shirt”—> returns TRUE.
- The SEARCH function will search for the value “green” and return the position of this text within cell C5.
- So, SEARCH(“green”, C5)—> returns #VALUE!.
- The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. Here it checks whether the SEARCH function’s result is a number or not.
- Thus, ISNUMBER(#VALUE!)—> returns FALSE.
- The OR function will return TRUE when any of the logic becomes TRUE.
- OR(TRUE, FALSE)—> returns TRUE.
- The IF function will return Found when the logical test is TRUE otherwise it will return Not Found.
- Drag the Fill Handle icon to copy the formula to the other cells of the column.
Method 6 – Merging AND Function with IF-THEN Statements to Search for a Text String
Suppose you want to know if is there any product which is both a shirt and any shade of the color green.
Steps:
- Enter the following formula in cell E5:
=IF(AND(B5="shirt",ISNUMBER(SEARCH("green",C5))),"Found", "Not Found")- Press ENTER.
Formula Breakdown
- The AND function will consider two logical tests. One is B5=”shirt” and other is ISNUMBER(SEARCH(“green”,C5)).
- B5= “shirt” will check whether the cell value of B5 is equal to shirt or not.
- B5= “shirt”—> returns TRUE.
- The SEARCH function will search for the value “green” and return the position of this text within the C5 cell.
- So, SEARCH(“green”, C5)—> returns #VALUE!.
- The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. Here it checks whether the SEARCH function’s result is a number or not.
- Thus, ISNUMBER(#VALUE!)—> returns FALSE.
- The AND function will return TRUE when both logical tests are TRUE.
- AND(TRUE, FALSE)—> turns FALSE.
- The IF function will return Found when the logical test is TRUE otherwise it will return Not Found.
- Drag the Fill Handle icon to copy the formula to the other cells of the column.
As a result, you will find which products are shirts with any shade of green color.
Method 7 – Multiple IF-THEN Statements to Get Logical Output Depending on Various Conditions
Suppose you want to know if any shirt has sea green color and L size.
Steps:
- Enter the following formula in cell E5:
=IF(B5="shirt",IF(C5="sea green",IF(D5="L","Found")))- Press ENTER.
Formula Breakdown
- Here, we have used a combination of three IF functions.
- IF(D5=”L”, “Found”) will check whether the cell value of D5 is equal to L or not. If the value is equal to L then it will return Found otherwise FALSE.
- Output: FALSE.
- IF(C5=”sea green”, FALSE) will check whether the cell value of C5 is equal to sea green or not. If the value is equal to sea green then it will return FALSE, based on the previous logic.
- Output: FALSE.
- IF(B5= “shirt”, FALSE) will check whether the cell value of B5 is equal toshirt or not. If the value is equal to shirt then it will return FALSE, based on the above logic.
- Output: FALSE.
- So, this is a kind of chain logical test, where if only one test fails you will get FALSE as output.
- Drag the Fill Handle icon to copy the used formula respectively to the other cells of the column.
Returned will be the shirt having sea green color with L size.
Download Practice Workbook
<< Go Back to Text | If Cell Contains | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
