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)
244 comments
I need to post a 1 in the subject cell, if another cell contains an A or a B (there may be other letters). I need to post a -1 in the subject cell if the other cell contains a D or an F. The subject cell remains blank if there is not an A, B, D, or F in the other cell. Thanks for any help.
Hello Jerry!
You can use the SEARCH function to search for a letter within a string of text. ISNUMBER function converts the result of the search to True or False. A double minus shows this as 0 or 1. The IF function returns the desired result if the sum of the results is greater than 0.
=IF(SUM(--ISNUMBER(SEARCH({"A","B"},A1))),1, IF(SUM(--ISNUMBER(SEARCH({"D","F"},A1))),-1,""))
I need to set up a IF function with a TEXT as the file I download the number comes out as text. A2 is the downloaded file. In A3 I need to show if A2 is 3000 then change to 24430, otherwise leave it as A2 (ie A2 is 3000 change to 24430, A3 is 3007 then leave as 3007, A4 is 3010 then leave as 3010)
Hello Andrew!
Please re-check the article above since it covers your task. The answer to your question can be found in this paragraph of the article above: If cell contains specific text.
If you want to return a value that is dependent on more than one condition in an IF formula, use this guide: Nested IF in Excel – formula with multiple conditions. For example:
=IF(A2="3000","24430",IF(A3="3007","3007",""))
I need help displaying a row which contains a particular value in a fresh sheet.
Hi! You can filter one or more rows by condition and display them in a new location using FILTER function.
You can find the examples and detailed instructions here: Excel FILTER function - dynamic filtering with formulas.
Hi, I am trying to use a easy "if" formula and something is not working. I have seen different options already.
So I have a cell with a number X which means the quantity I have in stock. Then I have another cell with the minimum quantity I should have (Y) and another cell with the maximum quantity I am allowed to order for stocking (Z). So I do IF (=IF(Y>X;Z-X;"No need"). This formula is plotted in a final cell to see the result but I always see "No need" text instead of any value when testing it.
I see everywhere using comas (,) but I can't, I am forced to use dot coma (;).
Could you please help me?
Thanks in advance.
Hello Antonio!
If I understood the question correctly, to calculate how many units you can order, try this formula:
=IF(Y1>X1,MIN(Z1,Y1-X1),"No need")
MIN function determines that you can order no more than the maximum quantity that you are allowed to order.
Hi there, I need to select a cell Z19 if another cell A9 is reading a certain text (eg 4m) but if it reads another text in the same cell (eg 3m) i need it to select another cell (Z18). This is what i have so far.
=IF(ISNUMBER(SEARCH("4m",A9)),Z18,"")
Hello Danny!
If I understand your task correctly, the following formula should work for you:
=IF(ISNUMBER(SEARCH("4m",A9)),Z19,Z18)
Hi there, I am replying to my own thread as I have found the solution to my question. With a bit of research i found this formula online and adjusted it slightly. Basically if the main cell reads 4m the cell I have selected will equal to another cell, if the same cell reads 3m again, the cell i have selected will equal to another cell and so on. I hope that makes sense.
=IF(COUNTIF(A9, "*4m*"), AG19, IF(COUNTIF(A9, "*3m*"), AG18, IF(COUNTIF(A9, "*2.5m*"), AG17, "")))
I need help to make a formula - IF the cell is not blank, THEN add the date into another cell. However I want the date to not change. Ie I want to be able to track when text was added to the first cell. Thanks.
Hello Kathryn!
If I understand your task correctly, this guide may be helpful: How to insert today date & current time as unchangeable time stamp
The second option is to use VBA to write the current date and time to the cell.
Perfect - thanks, I’ll give it a go.
I need formula where if i need to match a specific text, across multiple cells and if any of the cells match i need return the complete value from that cell.
Hi! You can use one of these instructions: XLOOKUP with partial match (wildcards) or VLOOKUP formula with wildcard or MATCH formula with wildcards. Use MATCH function with wildcards in INDEX MATCH formula.
Hi
I need a formula that reads a date and then moves the data of the row into a new sheet
(schedule via excel)
example if column A has 2.5.2024 move row B to sheet 2
What type of formula do I require?
Hi! If I understand the question correctly, you can search the column for the correct value and extract the corresponding row using INDEX MATCH functions. To extract not just one value, but the entire string from a range, use this instruction: Get all values in a row or column. You can correctly create references to another worksheet as described here: Excel reference to another sheet or workbook (external reference).
I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.
Please could you assist to not allow people to select more than one option. Drop down on both columns to select data or voice. So if a person selects 10GB data, the voice cell must be inactive . if they select voice, data must be inactive
Only Data Only Voice
10GB
125MIN
Hi! I don't really understand what it means for a cell to be inactive. If you want to restrict data entry to individual cells, you may find this tutorial helpful: Data validation in Excel: how to add, use and remove.
Hi! I am attempting to replace the contents of a cell with a number based on whether or not that same number is found in within the cell. Here is the formula I've attempted but I cannot figure out why it is consistently returning a "0" rather than the corresponding number:
=IF(C29(C29, "*1*"), 1, IF(COUNTIF(C29, "*2*"), C29, IF(COUNTIF(C29, "*3*"), 3, IF(COUNTIF(C29, "*4*"), 4, IF(COUNTIF(C29, "*5*"), 5, "")))))
Any help would be greatly appreciated!
Lacy
Hi! Your formula returns nothing because it contains an error. The formula may look like this if you use the COUNTIF function in all conditions:
=IF(COUNTIF(C29, "*1*"), 1, IF(COUNTIF(C29, "*2*"), C29, IF(COUNTIF(C29, "*3*"), 3, IF(COUNTIF(C29, "*4*"), 4, IF(COUNTIF(C29, "*5*"), 5, "")))))
This formula works only with text values. If C29 contains a number, the function returns 0.
It is also unclear what the formula should return if the text contains multiple numbers. For example, "1234".
To offer you a formula, explain your task in more detail.
When A column contains value then B column will write "Yes". When A Column is Blank then B Column "Pending" will be written. When A Column is 0.00 then B Column "NO" will be written.
Use conditional format
Hi! With conditional formatting, you cannot change the value of a cell by condition. Use the guidelines from the article above. This tutorial will also be useful: Nested IF in Excel – formula with multiple conditions. For example:
=IF(A1<>"","Yes",IF(A1=0,"No"))
You can also use the IFS function for multiple conditions:
=IFS(A1<>"","Yes",A1=0,"No")
Hi I have a table where a column captures days overdue (Table2[Ontime / Overdue])
In the same sheet I have a summary at the top showing [P1 etc is the cell it is in]:
[P1] Overdue by 1 Days
[P2] Overdue by 2 Days
[P3] Overdue by 3 Days
[P4] Overdue by 4 Days
[P5] Overdue by 5 Days
[P6] Overdue by >5 Days
(formula in Q1 etc follows =COUNTIF(Table2[Ontime / Overdue],P1) where P1 is a cell with text "Overdue by 1 Days". It is text and number
I am specifically looking to have the [Q6] "Overdue by >5 days" return how many cells have the text showing anything greater than 5 days, though I am having trouble having the formula calculate where it searches the cells (Table2[Ontime / Overdue] for where a number greater than 5 is present.
Any help is much appreciated.
Hi! If I understand your task correctly, you cannot conditionally count text strings that contain different numbers greater than 5. You can only compare to a number, not text.
Thank you for your response, that is good to know. I found a workaround by using another column to use standard count for (Table2[Ontime / Overdue]), then counting how many times that column had instances >5 :)
I have data in one column as bellow
Ram
Ram / Ram
Ram / Ram / Shyam
Shyam / Jay / raj
I want, if only ' Ram' in cell then only Ram
If multiple only ram in cell then multiple ram
If Ram with other then I want answer Ram
with other
If ram not available in cell then answer other
I want formula
Hi! Based on your description, it is hard to completely understand your task. You can count how many times the word "Ram" is found in the text. Split the text into words using the TEXTSPLIT function and count the number of the desired word using the SUMPRODUCT function. You can remove extra spaces with the TRIM function. For example:
=SUMPRODUCT(--(TRIM(TEXTSPLIT(A2,"/"))="Ram"))
Hello - I am sure an easy one but using the formula "=IF(AY11"", "1", "")" ... The resulting retunr of 1, even when formated to number isn't pivotable and is unable to sum the qty of one's on the said Pivot report.
Any ideas anyone would be greatly appreciated.
Hi! Sorry, I have no idea exactly what the task is.
It's hard to tell exactly what you're asking for as it's currently written.
NAME 20 D+ 20 C - 30 B 30 B 23 C 25 C+ - 20 148 C
in the mark list included mark and grade (total mark is 148 with grade)
i want min grade formula in excel
please
Hi! Unfortunately, this information is not enough to give you any advice. You can determine the minimum value with the MIN function. If this does not help, explain the problem in detail.
for eg: five cells contains 1,5,9,3,6. in this we can find the smallest value with the formula
=SMALL(D9:Q9,1)
then, another cells contains A,A+,B,B+. In this format which formula we can?
Hi! I don't understand what you want to do with five numbers and four letters.
Maybe this article will be helpful: MINIFS function in Microsoft Excel – syntax and formula examples
Hello!
I have a question about VLOOPUP(lookup_value, table_array, col_index_num, [range_lookup]). Can we use an array in the "lookup_value"? Something like {"string1";"string2";"string3"}. I just have a long list of complex names, which are sometimes different, but mean the same thing. And in order to match them to the right IDs I need a complex search not out of one word, but multiple. How can I do it? Maybe not VLOOKUP, but something else?
Hi! If you use {"string1"; "string2"; "string3"} as lookup_value, you will get an array of three search results. This formula will return TRUE if at least one value is found:
=SUM(--NOT(ISNA(VLOOKUP({"aaa";"bbb"},B2:C30,2,FALSE))))>0
For more information, read: ISNA function with VLOOKUP.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Thanks, but it looks like I phrased the question poorly. I ment if it is possible to have {"string1";"string2";"string3"} as one of the options in the table_array. So that VLOOKUP would have different variations of one name for each ID. Because now when I put a certain name as lookup_value and then put in one cell of the table_array something like {"1_variation";"2_variation" (correct one);"3_variation"}, it gives me N/A, instead of ID, whcih I was looking for. And if I put TRUE as [range_lookup], then sometimes it gives the wrong answer because of alphabetic structure. Sorry for the inconviniece.
Hi! You can write all the variations in one cell as a text string. For example, "1_variation 2_variation 3_variation". Use the TEXTSPLIT function to get an array of variations from this text string. The formula might look something like this:
=SUM(--NOT(ISNA(VLOOKUP(TEXTSPLIT(H1," "),B2:C30,2,FALSE))))>0
I hope it’ll be helpful.
Thank you again! Maybe I am getting something wrong, but your formula has TEXTSPLIT in lookup_value, but variations are inside of the values of table_array. I have multiple versions of one name for each ID in order to cover different spelling and those variations are in the table_array, together with their respective IDs (B2:C30 in your formula). It looks like {"1_variation";"2_variation";"3_variation"} | ID. Lookup_value is just a name that should have an ID from the B2:C30, depending on spelling. Also, your formula returns Bool, but I need ID itself. Is there a way to do it?
Hi! Each of your new questions is different from the previous question. To give you an accurate answer, give an example of the source data and the result you want to get. Describe the problem accurately and in detail.
Hi! I said "I ment if it is possible to have {"string1";"string2";"string3"} as one of the options in the table_array" and "but your formula has TEXTSPLIT in lookup_value, but variations are inside of the values of table_array", so I did't really say anyting different, but if it still is not clear for you, then I will give this example:
| | | |
№| A | B | C | D |
-----------------------------------------------------------------------------------------------------------------------------------------
| | | |
| | | |
1 | 2_var_for_tag1 | VLOOKUP(A1,C1:D10,2, true) -> tag1 | {"1_var_for_tag1";"2_var_for_tag1";"3_var_for_tag1"} | tag1 |
| | | |
2 | 3_var_for_tag2 | VLOOKUP(A1,C1:D10,2, true) -> tag2 | {"1_var_for_tag2";"2_var_for_tag2";"3_var_for_tag2"} | tag2 |
| | | |
3 | 1_var_for_tag3 | VLOOKUP(A1,C1:D10,2, true) -> tag3 | {"1_var_for_tag3";"2_var_for_tag3";"3_var_for_tag3"} | tag3 |
| | | |
...
And so on! Simply adding a new row for each variation won't work, because I have too many rows with data. Hope it will help!
Hi! If I understand your task correctly, try the following formula:
=INDEX($D$1:$D$10,MATCH(TRUE,ISNUMBER(SEARCH(A1,$C$1:$C$10)),0))
For more information, read: How to find substring in Excel
Hi!
I have a fairly specific case and any help would be greatly appreciated. There is a list of signal names that may be slightly different but mean the same thing, for example in column A I have:
Tempreture in the boiler
T in the boiler
Pressure in the tube
Tube pressure
P in the tube
And for each signal there is a list of tags that are always the same, for example they are in column F:
TmBl
PrTb
I need to map different versions of the same signal to its tag in the next column, including the possibility of different spellings. So you should end up with something like this:
Tempreture in the boiler TmBl
T in the boiler TmBl
Pressure in the tube PrTb
Tube pressure PrTb
P in the tube PrTb
I know that I can do this with =IF(COUNTIF(A1;"*Signal1*");"Tag1";IF(COUNTIF(...) ) , but this approach is too long and messy in case I have 50 signals and 150 variations of them. Is there another convenient solution? Maybe I could have some kind of dictionary with keywords for each signal or something like that?
Thank You in advance!
Hi! You can create a table with keywords for each signal. Column A is the signal, column B is the tag. You can use the VLOOKUP or INDEX MATCH function to search for a tag by signal name.
Thank you for the reply, but I am not sure if you can use VLOOKUP or INDEX MATCH with the multiple keywords search. For example, I might have something like:
T after the boiler
Temperature after the boiler
T before the boiler before the valve
T before the boiler after the valve
Which means that if my keyword will be only "T", then I will get only the first option. And if I use such keywords as:
("T", "after", "boiler")
("T", "before", "boiler", "valve")
("T", "before", "boiler", "after", "valve")
Then I will loose the second record where I have "Temprature" instead of "T". So it should be something like:
("T", "Temperature", "after", "boiler")
("T", "before", "boiler", "valve")
("T", "before", "boiler", "after", "valve")
Which means that the formula should be able to take either "T" or "Temperature" AND other words in order to get it right. Is there a way to do this in Excel?
Hi! If each set of keywords will be written in a single cell, then you can use the instructions I recommended. If each keyword will be written in a separate cell in a row, you can use these instructions to search for a tag based on multiple keywords: Excel INDEX MATCH with multiple criteria or How to Vlookup multiple criteria in Excel.
But I recommend writing the key string in a single cell.
I am trying to identify the column # of a cell within a range that contains 2 different strings of text (ordered if necessary), but those are not the only strings within the cell.
e.g. A1 = string 1, string 2, string 3; A2 = string 1, string 4, string 5; A3 = string 3, string 4, string 6
I would like the formula to look at the range A1:A3 and return the position of the cell with string 1 and string 5. As noted above it can be written so they are ordered or unordered, if one is easier than the other.
Hi! If I understand the question correctly, you can determine the position of the cell that has the text strings "string 1" and "string 5" using the MATCH formula. For example:
=MATCH(1,ISNUMBER(SEARCH("string 1",A1:A3)) * ISNUMBER(SEARCH("string 5",A1:A3)),0)
For more information, please read: How to find substring in Excel
I have a sheet with a list of clients and a list of the companies they work for. Is there a way to say if the cell is equal to a company name to then show the employee name on a separate sheet??
Hi! When you want to find a value and get its corresponding value from another column, use these guidelines and examples: Excel VLOOKUP function tutorial with formula examples and INDEX & MATCH in Excel - better alternative to VLOOKUP.
let say i have a cell that contain "Monday morning, tuesday morning" but by using the if function who do i only say yes to one of them which is Monday?