by The tutorial provides a number of "Excel if contains" formula examples that show how to return something in another column if a target cell contains a required value, how to search with partial match and test multiple criteria with OR as well as AND logic.
One of the most common tasks in Excel is checking whether a cell contains a value of interest. What kind of value can that be? Just any text or number, specific text, or any value at all (not empty cell).
There exist several variations of "If cell contains" formula in Excel, depending on exactly what values you want to find. Generally, you will use the IF function to do a logical test, and return one value when the condition is met (cell contains) and/or another value when the condition is not met (cell does not contain). The below examples cover the most frequent scenarios.
If cell contains any value, then
For starters, let's see how to find cells that contain anything at all: any text, number, or date. For this, we are going to use a simple IF formula that checks for non-blank cells.
For example, to return "Not blank" in column B if column A's cell in the same row contains any value, you enter the following formula in B2, and then double click the small green square in the lower-right corner to copy the formula down the column:
=IF(A2<>"", "Not blank", "")
The result will look similar to this:
If cell contains text, then
If you want to find only cells with text values ignoring numbers and dates, then use IF in combination with the ISTEXT function. Here's the generic formula to return some value in another cell if a target cell contains any text:
Supposing, you want to insert the word "yes" in column B if a cell in column A contains text. To have it done, put the following formula in B2:
=IF(ISTEXT(A2), "Yes", "")
If cell contains number, then
In a similar fashion, you can identify cells with numeric values (numbers and dates). For this, use the IF function together with ISNUMBER:
The following formula returns "yes" in column B if a corresponding cell in column A contains any number:
=IF(ISNUMBER(A2), "Yes", "")
If cell contains specific text
Finding cells containing certain text (or numbers or dates) is easy. You write a regular IF formula that checks whether a target cell contains the desired text, and type the text to return in the value_if_true argument.
For example, to find out if cell A2 contains "apples", use this formula:
=IF(A2="apples", "Yes", "")
If cell does not contain specific text
If you are looking for the opposite result, i.e. return some value to another column if a target cell does not contain the specified text ("apples"), then do one of the following.
Supply an empty string ("") in the value_if_true argument, and text to return in the value_if_false argument:
=IF(A2="apples", "", "Not apples")
Or, put the "not equal to" operator in logical_test and text to return in value_if_true:
=IF(A2<>"apples", "Not apples", "")
Either way, the formula will produce this result:
If cell contains text: case-sensitive formula
To force your formula to distinguish between uppercase and lowercase characters, use the EXACT function that checks whether two text strings are exactly equal, including the letter case:
=IF(EXACT(A2,"APPLES"), "Yes", "")
You can also input the model text string in some cell (say in C1), fix the cell reference with the $ sign ($C$1), and compare the target cell with that cell:
=IF(EXACT(A2,$C$1), "Yes", "")
If cell contains specific text string (partial match)
We have finished with trivial tasks and move on to more challenging and interesting ones :) To check if a cell contains specific a given character or substring as part of the cell content, you can use one of these formulas:
Formula 1
Working from the inside out, here is what the formula does:
- The SEARCH function searches for a text string, and if the string is found, returns the position of the first character, the #VALUE! error otherwise.
- The ISNUMBER function checks whether SEARCH succeeded or failed. If SEARCH has returned any number, ISNUMBER returns TRUE. If SEARCH results in an error, ISNUMBER returns FALSE.
- Finally, the IF function returns the specified value for cells that have TRUE in the logical test, an empty string ("") otherwise.
Formula 2
Here, the COUNTIF function finds out how many times a certain text appears in a cell. To count partial matches, you place the wildcard character (*) on both sides of the text. If the count is greater than zero, then IF returns the specified value, otherwise - a blank cell.
And now, let's see how this generic formula works in real-life worksheets.
If cell contains certain text, put a value in another cell
Supposing you have a list of orders in column A and you want to find orders with a specific identifier, say "A-". The task can be accomplished with this formula:
=IF(ISNUMBER(SEARCH("A-", A2)), "Valid", "")
or
=IF(COUNTIF(A2, "*"&"A-"&"*"), "Valid", "")
Instead of hardcoding the string in the formula, you can input it in a separate cell (E1), the reference that cell in your formula:
=IF(ISNUMBER(SEARCH($E$1,A2)), "Valid", "")
or
=IF(COUNTIF(A2, "*"&$E$1&"*"), "Valid", "")
For the formula to work correctly, be sure to lock the address of the cell containing the string with the $ sign (absolute cell reference).
If cell contains specific text, copy it to another column
If you wish to copy the contents of the valid cells somewhere else, simply supply the address of the evaluated cell (A2) in the value_if_true argument:
=IF(ISNUMBER(SEARCH($E$1,A2)),A2,"")
The screenshot below shows the results:
If cell contains specific text: case-sensitive formula
In both of the above examples, the formulas are case-insensitive. In situations when you work with case-sensitive data, use the FIND function instead of SEARCH to distinguish the character case.
For example, the following formula will identify only orders with the uppercase "A-" ignoring lowercase "a-".
=IF(ISNUMBER(FIND("A-",A2)),"Valid","")
If cell contains, then return value – multiple conditions
A single ‘if cell contains’ statement is easy, right? But what if you need to check multiple conditions in the same formula and return different results? That is, if a cell contains some text, then return something, if it contains another text, then return something else, and so on.
To evaluate multiple conditions in a cell, you can use nested "if cell contains" statements. This allows you to create a chain of checks, with each condition leading to a specific result. Here are two generic formulas to achieve this:
Formula 1
Use the COUNTIF function to count how many times a certain text appears in a cell. If the count is greater than zero, then return the corresponding value. Otherwise, check the next condition.
Formula 2
Use the SEARCH function to find the position of a certain text in a cell. If the position is a number, then return the corresponding value. Otherwise, check the next condition.
For example, to checks if cell A2 contains "apple", "banana" or "lemon" and return the corresponding name of the fruit in cell B3, you can use one of these formulas:
=IF(COUNTIF(A2, "*apple*"), "Apple", IF(COUNTIF(A2, "*Banana*"), "Banana", IF(COUNTIF(A2, "*lemon*"), "Lemon", "")))
=IF(ISNUMBER(SEARCH("apple", A2)), "Apple", IF(ISNUMBER(SEARCH("banana", A2)), "Banana", IF(ISNUMBER(SEARCH("lemon", A2)), "Lemon", "")))
To fit your specific needs, you can extend the chain of conditions as necessary to handle more cases.
If cell contains one of many text strings (OR logic)
To identify cells containing at least one of many things you are searching for, use one of the following formulas.
IF OR ISNUMBER SEARCH formula
The most obvious approach would be to check for each substring individually and have the OR function return TRUE in the logical test of the IF formula if at least one substring is found:
Supposing you have a list of SKUs in column A and you want to find those that include either "dress" or "skirt". You can have it done by using this formula:
=IF(OR(ISNUMBER(SEARCH("dress",A2)),ISNUMBER(SEARCH("skirt",A2))),"Valid ","")
The formula works pretty well for a couple of items, but it's certainly not the way to go if you want to check for many things. In this case, a better approach would be using the SUMPRODUCT function as shown in the next example.
SUMPRODUCT ISNUMBER SEARCH formula
If you are dealing with multiple text strings, searching for each string individually would make your formula too long and difficult to read. A more elegant solution would be embedding the ISNUMBER SEARCH combination into the SUMPRODUCT function, and see if the result is greater than zero:
For example, to find out if A2 contains any of the words input in cells D2:D4, use this formula:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0
Alternatively, you can create a named range containing the strings to search for, or supply the words directly in the formula:
=SUMPRODUCT(--ISNUMBER(SEARCH({"dress","skirt","jeans"},A2)))>0
Either way, the result will be similar to this:
To make the output more user-friendly, you can nest the above formula into the IF function and return your own text instead of the TRUE/FALSE values:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0, "Valid", "")
How this formula works
At the core, you use ISNUMBER together with SEARCH as explained in the previous example. In this case, the search results are represented in the form of an array like {TRUE;FALSE;FALSE}. If a cell contains at least one of the specified substrings, there will be TRUE in the array. The double unary operator (--) coerces the TRUE / FALSE values to 1 and 0, respectively, and delivers an array like {1;0;0}. Finally, the SUMPRODUCT function adds up the numbers, and we pick out cells where the result is greater than zero.
If cell contains several strings (AND logic)
In situations when you want to find cells containing all of the specified text strings, use the already familiar ISNUMBER SEARCH combination together with IF AND:
For example, you can find SKUs containing both "dress" and "blue" with this formula:
=IF(AND(ISNUMBER(SEARCH("dress",A2)),ISNUMBER(SEARCH("blue",A2))),"Valid ","")
Or, you can type the strings in separate cells and reference those cells in your formula:
=IF(AND(ISNUMBER(SEARCH($D$2,A2)),ISNUMBER(SEARCH($E$2,A2))),"Valid ","")
As an alternative solution, you can count the occurrences of each string and check if each count is greater than zero:
=IF(AND(COUNTIF(A2,"*dress*")>0,COUNTIF(A2,"*blue*")>0),"Valid","")
The result will be exactly like shown in the screenshot above.
How to return different results based on cell value
In case you want to compare each cell in the target column against another list of items and return a different value for each match, use one of the following approaches.
Nested IFs
The logic of the nested IF formula is as simple as this: you use a separate IF function to test each condition, and return different values depending on the results of those tests.
Supposing you have a list of items in column A and you want to have their abbreviations in column B. To have it done, use the following formula:
=IF(A2="apple", "Ap", IF(A2="avocado", "Av", IF(A2="banana", "B", IF(A2="lemon", "L", ""))))
For full details about nested IF's syntax and logic, please see Excel nested IF - multiple conditions in a single formula.
Lookup formula
If you are looking for a more compact and better understandable formula, use the LOOKUP function with lookup and return values supplied as vertical array constants:
For accurate results, be sure to list the lookup values in alphabetical order, from A to Z.
=LOOKUP(A2,{"apple";"avocado";"banana";"lemon"},{"Ap";"Av";"B";"L"})
For more information, please see Lookup formula as an alternative to nested IFs.
Vlookup formula
When working with a variable data set, it may be more convenient to input a list of matches in separate cells and retrieve them by using a Vlookup formula, e.g.:
=VLOOKUP(A2, $D$2:$E$5, 2,FALSE )
For more information, please see Excel VLOOKUP tutorial for beginners.
This is how you check if a cell contains any value or specific text in Excel. Next week, we are going to continue looking at Excel's If cell contains formulas and learn how to count or sum relevant cells, copy or remove entire rows containing those cells, and more. Please stay tuned!
Practice workbook
Excel If Cell Contains - formula examples (.xlsx file)
240 comments
Hello,
I am trying to figure out a formula that will tell me if one cell partially contains the same info as another cell.
Example:
If A2 has "PleaseHelpMe" and B2 has "Please"
I want a formula that will do the following IF A2 contains B2 = "yes" or "no"
Hopefully that makes sense.
Hello!
The formula below will do the trick for you:
=IF(ISNUMBER(SEARCH(B2,A2,1)),"Yes","No")
Alexander,
You are awesome! Thank you, this will help out tremendously on a project I am working on.
Hello! I have a large spreadsheet (300k rows) with clients details, unfortunately the data is from a form where people were simply asked to enter their City & Country. So they may have entered Christchurch NZ, or Auckland New Zealand, or Los Angeles USA etc etc
We now wish to be able to add a new column that specifies the country ONLY for each client.
What is the best approach for this?
Ideally we would like to be able to have one formula that can search for multiple countries, so for example, if cell A2 contains "NZ" OR "New Zealand" the value in the new column shows as = New Zealand, if the A2 contains "United States" or "US" or "USA" or "America" the value in the new column shows as = USA. everything I have tried so far says it is too long, so I assume I need to work out how to use Vlookup? Is this what it will do!?
Obviously there is a huge array of possibilities, is it possible to have SO many variables? Thank you!
Hello!
You will be looking for a piece of text in a cell. Therefore VLOOKUP cannot be used here.
Try this formula:
=INDEX(F1:F30,MATCH(TRUE,ISNUMBER(SEARCH(E1:E30,A1,1)),0))
Column F - correct country names (e.g. New Zeland)
Column E - arbitrary country names (e.g. NZ)
Column A is your data (e.g. Christchurch NZ).
I hope I answered your question. If something is still unclear, please feel free to ask.
Thank you. your formula assisted me in resolving my pain area.
I have a chart with 2 columns, 1 column - Month 2nd column - Amounts
how do I create a formula that pulls out or subtract out the amounts for a certain month.
EXAMPLE: February amount needs to be subtracted from Jan, Mar, April.
January 150
February 200
March 500
April 2000
I know to sum up everything and then subtract, but I don't know how to create the formula to do this on its own.
Hello!
If I understand your task correctly, the following formula should work for you:
=SUM(B1:B4)-B2
If this is not what you wanted, please describe the problem in more detail.
Hello,
I would like to match 3 cells in two separate tabs (same file), cells A (tab 1), and cells B & C (tab 2).
Cells A & C are a string of words. Cell B is one word.
If one of the words in cell C is found in cell A, then the formula returns cell B.
I'm going through a bank statement and doing some budgeting based on categories I've created.
For example:
1/ Tab 2: Category "Utilities" (cell B) = Water, Council, BT (cell C)
2/ Tab 1 (bank statement) (cell A): DIRECT DEBIT PAYMENT TO WATER REF 400000214, MANDATE NO 0125
I haven't been able to work out the formula to use to bring back the value of cell B in a new column added to the statement (tab 1).
Might you be able to help please?
Many thanks,
Cecile
Hello!
To search for a word in cell A, you must first split text in cell C into individual words. This cannot be done with one formula.
Hi Alexander,
Thanks for your feedback. I was hoping for a shortcut, never mind!
Once I've split the text, shall I then use the following formula:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Tab2CAtegoryList!$a$2:$a10,Tab1Statement!C2))),"categoryName1","No")
Thanks,
Cecile
Hello!
Yes, you can use something like this.
IFS function can be used to choose from several categories
=IFS(SUMPRODUCT(–ISNUMBER(SEARCH(Tab2CAtegoryList!$a$2:$a10,Tab1Statement!C2))),"categoryName1", SUMPRODUCT(–ISNUMBER(SEARCH(Tab2CAtegoryList!$b$2:$b10,Tab1Statement!C2))),"categoryName2")
Hello
I have an incident where Students are graded in a subject per week from week one to week 16. where each week is in its own column from Column B to Column P. where I grade each student with words "Passed" and "Excelled". I'm crediting their overall performance in another sheet which fetches from my primary sheet and I would like help with an excel function that searches for a word "excelled" in any week from week one to 16 and returns "promoted" if it finds the word "excelled" any where. Thanks!
Hello!
To search for values in the desired line, I recommend using such a formula:
=SUMPRODUCT(--(A2:A20=N1)*(B2:P20= "Excelled"))
N1 - name.
If the formula returns the number more than 0, then the desired word is found for this student.
I hope it’ll be helpful.
in excel want a cell contains formula should remain as it is if changes made manually in the same cell.
for example Customer A 50 (formulated cell)
Manual changes done is the count cell as 40
If I selecect customer B from drop down the formulated cell should remain same
Is there any possibility to do this
Hi!
Based on your description, it is hard to completely understand your task. Perhaps this article will be useful to you: How to lock and hide formulas in Excel
I.e. I have cell K2 containing 2 different categories (OWN & LEASE). If cell K2 is "OWN" I want to add values from cell P2+R2+S2 or if cell K2 is "LEASE" then I only want to add values from Q2
Hello!
Please have a look at this article — Excel IF statement with nested IF formulas
The formula below will do the trick for you:
=IF(K2="OWN",P2+R2+S2,IF(K2="LEASE",Q2,""))
=index(A:A,MATCH(C:C,A:A,0))
my column A contains the following formula:
=INDIRECT("'"&$D$2&"'!"&Parameters!N$2&$C22)
The formula gets values from another tab based on certain parameters.
Using conditional formatting I want to make sure the indirect formula exists in all the cells and no one has tempered with the formula. Is there a way to conditional format the column A to highlight cells using the Indirect formula?
Thanks
KK
Hello!
It is very difficult to understand a formula that contains unique references to your workbook worksheets. For the same reason, I cannot check her work.
I recommend using the FORMULATEXT function. It will extract the formula from the desired cell and write it down as text. You can compare this text with your formula if you also write it as text.
Hi there, hoping I didn't miss this explained above or in the comments but here's what i'm trying to figure out:
I have a list of titles in multiple rows of column I (Ex. I2 contains Associate, Manager, Senior Manager, Vice President. I3 contains Associate, Senior Manager, Vice President).
I am using the following formula to separate each title into separate columns for each Row, if it is listed in "I" : =IF(ISNUMBER(SEARCH("Associate",I2)),"Associate") but I'm finding that when using the formula for Manager it is giving me a false positive because "Senior Manager" contains the word "Manager" (is should result in "FALSE").
Basically, is there a way to add an exclusion for the word "senior" in the formula?
Thanks!
Hello Meghan!
If I understand your task correctly, the following formula should work for you:
=IF(ISNUMBER(SEARCH("Manager",I3)), IF(ISNUMBER(SEARCH("Senior Manager",I3)),FALSE,"Associate"))
I hope this will help
Hi, upon some extensive research I couldn't find any answer to my problem. Here it is. Every month, I breakdown my transactions (from bank statements) according to their nature such as food, shopping and entertainment. So I prepared a key word mapping with natures as follows:
Column A - Column B
- Walmart - shopping
- Netlix - entertainment
- McDonalds - food
in two column in excel (list is pretty long, examples are simplified). Each transaction records includes many words and details of the transaction. What I want is, if a transaction includes the key word of **NETFLIX**, I need to bring word **ENTERTAINMENT** as nature next to the transaction column in excel. Example: if transaction is "*...June charge of **NETFLIX** obtained from credit card 12345...*" the key word **NETFLIX** is included in the transaction details, then bring **ENTERTAINMENT**. Had I kept my list short, I would have done it with =if(isnumber(search(... formula but my list is looong.
PS. Just extracting out the key word next to the transaction columnn would be fine, too. As the rest can be done by Vlookup.
I need your help.
Hello,
I have a table in which I'm trying to match cities to markets. For example if I have a certain "City Name" in column G, I want a certain "Area name" in column J. I have tried all sorts of variations on this formula but am still a beginner and none of them seem to work.
Noting that there would me multiple cities in column G to match the same area in column J.
Any bit of help would be much appreciated. Thank you!
Hi, Please can someone assist with a formula that checks 2 separate columns and if the text is an exact match then it must return a a text from the separate column and also a value to the correct column.
-3.1441E-07x5
+ 9.9711E-05x4
- 1.2506E-02x3
+ 7.4496E-01x2
- 1.4013E+01x
+ 8.0642E+01
I am trying to use =IF(ISNUMBER(SEARCH("x",A1)), "LEFT(A1,FIND("x",A1)-1","A1") formula to look at each set of data and see it there is an "x" in the data string. If there is, I need to delete everything on the left of the "x", including the "x". However, if there is no 'x' I need the formula to sinply copy the data string as is, to the next cell. I may not have everthing just right but I now that the ISNUMBER(SEARCH is correct, I get TRUE, FALSE as I should. I have not been able to pair the formula with the rest, due to excel assuming that the "x" in the Left/Find statement is supposed to be a "*". Is there a way around this?
Thank you
HI, I need help with totaling up a number meeting a certain percent and the ablity to excluding any zeros. I can do the countif to obtain the percentage that achieves the required percent but I don't know how to exclude the zeros.
example: % achieving 80% w/o "0"
100
85
0
80
80
0
I have a budgeting document that reflects a few sub-budgets. I want there to be an overall balance column (G) and then 3 sub-budgets (I/J/K). I'd like a formula in I/J/K that states the following:
If column B includes the following partial text xxx (the budget indicator code), then subtract F from the row above it. (F is the amount spent).
The column B will include things like TS1, TS2, TS3 or HS1, HS2, HS3. (The TS and HS are the partial texts I want it to look for - with columns I and J being the TS and HS balance columns.)
Hi , I have a list of products with the cost for each product is written in the adjacent cell . what I want is that when I type the name of the product in another worksheet , the value of the cost appears automatically . Can anybody please help me to find the exact equation for that ? thanks in advance
Hello
Is there a way of using the below formula, but rather than have it search for the specific text only within a cell, it can search a sentence containing "apple" or "banana" etc then return the value based on the sentence content? I need the formula to be able to search for multiple fruits and return the value in another cell depending on what fruit it found within the sentence.
For example, cell A1 contains the sentence, "Mr Smith ate an apple".
cell B1 should then return Apple. However, if cell A1 contained, "Mr Smith ate a banana", cell B2 should return "Banana".
=IF(A2="apple", "Ap", IF(A2="avocado", "Av", IF(A2="banana", "B", IF(A2="lemon", "L", ""))))
Hope this makes sense!
Thank you
Hi Rhys,
COUNTIF with wildcards in the criteria works a treat:
=IF(COUNTIF(A2, "*apple*")>0, "Ap", IF(COUNTIF(A2, "*avocado*")>0, "Av", IF(COUNTIF(A2, "*banana*")>0, "B", IF(COUNTIF(A2, "*lemon*")>0, "L", ""))))
Thanks so much! Worked perfectly.
what would I use in the formula to lookup if a cell has text or number? (replacing the ISNUMBER) ISTEXT will not work as the cell can contain text or a number.
=IFERROR(IF(B17="","",IF(ISNUMBER(INDEX(T_E,MATCH(I_E,L_E,0),MATCH("ACT "&B17&" DT",L_H,0))),"R",CHAR(163))),"")
In your MATCH formula, what is the T_E , I_E and L_E? I believe that should be a range, but what range is it referring to?
Hello,
I have numbers on Column A1 that I need B1 to return with a name if the number matches
For example
A1 is 118 and B1 needs to be Chad
A1 is 132 and B1 needs to be Mike
A1 is 109 and B1 needs to be Tuan
A1 is 110 and B1 needs to be Kevin
A1 is 115 and B1 needs to be Carlos
A1 is 105 and B1 needs to be Mark
A1 is 107 and B1 needs to be Curtis
and so on, I have been fighting this all afternoon.
Use VLOOKUP formula