I have 3 columns:
A - Lookup item B - List C - Link
D - formula goes here
I want to search column B for the text in each cell in column A.
For example, A2="bread" and with column B it has B2="some bread" and would return TRUE. If it's TRUE, then I want to return the value in C2="abc".
Another example, A3="eggs" and B3="egg" would return FALSE. But B4="eggs" and would return TRUE, then I want to return value in D3="ghi".
Here's an image to illustrate:
I can find the word in column A to any in the list in column be with this formula:
=if[countif[$B$2:$B$6,"*"&A2&"*"]>0,true,false]But I can't get the right mix of INDEX and MATCH to return the contents in column C when needed. I think I need a mix of INDEX with ROW instead of MATCH.
The issue with MATCH is the 3rd argument is [match_type] which is "1= exact or next smallest[default]", "0=exact match", "-1 = exact or next largest".
Version: Excel 2016 [also would like it to work in Google Sheets but not required]
As the example shown below, when cell E6 contains the value “Yes”, cell F6 will be automatically populated with the value “approve”. If you change “Yes” to “No” or “Neutrality” in E6, the value in F6 will be changed to “Deny” or “Reconsider” immediately. How can you do to achieve it? This article collects some useful methods to help you easily solve it.
Return value in another cell if a cell contains certain text with formula
For returning value in another cell if a cell contains only a specific text, please try the following formula. For example, if B5 contains “Yes”, then return “Approve” in D5, otherwise, return “No qualify”. Please do as follows.
Select D5 and copy below formula into it and press the Enter key. See screenshot:
Formula: Return value in another cell if a cell contains certain text
=IF[ISNUMBER[SEARCH["Yes",D5]],"Approve","No qualify"]
Notes:
1. In the formula, “Yes”, D5, “approve” and “No qualify” indicate that if cell B5 contains text “Yes”, the specified cell will be populated with text “approve”, otherwise, it will be filled with “No qualify”. You can change them based on your needs.
2. For returning value from another cells [such as K8 and K9] based on a specified cell value, please use this formula:
=IF[ISNUMBER[SEARCH["Yes",D5]],K8,K9]
Return values in another cell if a cell contains different texts with formula
This section is going to show you the formula for returning values in another cell if a cell contains different text in Excel.
1. You need to create a table with the specific values and return values that located separately in two columns. See screenshot:
2. Select a blank cell for returning the value, type the below formula into it and press the Enter key to get the result. See screenshot:
Formula: Return values in another cell if a cell contains different texts
=VLOOKUP[E6,B5:C7,2,FALSE]
Notes:
In the formula, E6 is the cell contains the specific value you will return value based on, B5:C7 is the column range containing the specific values and the return values, the 2 number means that the return values locating on the second column in the table range.
From now on, when changing the value in E6 to a specific one, its corresponding value will be returned in F6 immediately.
Easily return values in another cell if a cell contains different texts
Actually, you can solve the above problem by an easier way. The Look for a value in list utility of Kutools for Excel can help you to achieve it with only several clicks without remembering formula.
1. The same as the above method, you also need to create a table with the specific values and return values that locate separately in two columns.
2. Select a blank cell to output the result [here I select F6], and then click Kutools > Formula Helper > Formula Helper. See screenshot:
3. In the Formula Helper dialog box, please configure as follows:
- 3.1 In the Choose a formula box, find and select Look for a value in list;
Tips: You can check the Filter box, enter certain word into the text box to filter the formula quickly. - 3.2 In the Table_array box, select the table without headers you have created in step 1;
- 3.2 In the Lookup_value box, select the cell contains the specific value you will return value based on;
- 3.3 In the Column box, specify the column you will return the matched value from. Or you can enter the column number into the textbox directly as you need.
- 3.4 Click the OK button. See screenshot:
From now on, when changing the value in E6 to a specific one, its corresponding value will be returned in F6 immediately. See result as below:
If you want to have a free trial [ 30-day] of this utility, please click to download it, and then go to apply the operation according above steps.
No ratings yet. Be the first to rate!
Here is the Excel Formula If Cell Contains Text Then to Return Value in Another Cell. This is the very useful formula when we deal with strings. We can check if a cell contains a string value and write something in another cell or adjacent column. The following examples shows you the different scenarios with formulas to create strings in a new column based on the data in another column.
In this topic:
Excel Formula to Check If a Cell Contains Text Then Return Value in Another Cell
Here is the Excel formula if cell contains text then return value in another cell. Let us say, we have input data in Cell A1 and We want to Return Value in Another Cell B1. Excel formula for this Criteria is:
=IF[A1="My Text To Check", "My Text To Return", "NOT My Text"]
This formula will check If Cell Contains Text Then Return Value. In this If formula, we have three parameters. Here is the detailed explanation of these three parts of the above If Formula.
- Parameter 1: A1=”My Text To Check”, this will check the Value of Cell A1 with your required Text. For Example, “My Text To Check”
- Parameter 2: “My Text To Return”, this is the value which you want to return in another Cell if Matches with Cell A1 Text. For Example, “My Text To Return”
- Parameter 3: “NOT My Text”, this is the value which you want to return in another Cell if Not Matches with Cell A1 Text. For Example, “NOT My Text”
You can download this example Formula at the end of this topic.
Tracing of Excel Formula Calculation Steps
So, this Formula is performing total three different calculation steps. Here is how this formula is working:
- Calculations Step 1: The first step is checking If the cell value is matching with the given string or not.
- Calculations Step 2: And the second step is returning another value if matches.
- Calculations Step 3: And the final step is returning another sting if the target value is not matching with the cell value.
Let us trace this formula by each step. The following formulas will show you how to check If a Cell is matching with another Text. And return a value if the result is TRUE. Also return another string if the result is FALSE.
Excel Formula to check If a Cell Contains specific Text
Let us check if the text in the string matching with a given string. We can use simple Excel expression to check this. We can use Equals to [=] operator to check if value is same as a given value.
="Sting 1"="String 2"
The above formula will check if Sting 1 is equals to String 2 or Not. This will return FALSE.
Now, let us Check the Cell Value is matching with a Text or Not. The following formula will check if the Cell Value in A1 is same as “My String”.
=A1="My String"
If the Sting in Cell A1 is “My String”, The above formula will return TRUE, Else FALSE.
Formula to Return a Value Based on a Conditions
We have seen how to check if a Cell value is matching with given string or not. Let us see how to return another string based on the result.
The below example will show you Excel Formula to check If a Cell Contains Text Then Return Value in Another Cell. Let us return the Value in C1. And Check the Cell A1 for required string. We need to use IF formula to compare cell value with a string and return in another Value.
=IF[A1="My String", "My Return Value",""]
The above formula will check Cell A1 with “My String” and return another string “My Return Value” in Cell C1 if matches, returns blank [“”] if not matches.
Let us see another Excel Formula to check if Cell A1 is matching with B1, if matches then rerun the Value in C1, if not matches return the Value in D1. Let us enter the below formula in Cell E1.
=IF[A1=B1,C1,D1]
Explanation
The first part of the Excel If formula will check if A1 and A2 are equal or not. And the Second Part is the return value if true, it will return the value in C1 if matches. And the third part is return value if FALSE, it will return the value in D1 if not matches.
Download Example Excel File with Formula
Here is the Example file with Excel Formula to check If a Cell Contains Text Then Return Value in Another Cell. You can download the file and explore the Example Formulas in the Spreadsheet.
Excel Formula If Cell Contains Text Then Return Value in Another Cell