Skip to main content

How to return value in another cell if a cell contains certain text in Excel?

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)

Easily select entire rows or entire rows in the selection based on cell value in a certain column:

The Select Specific Cells utility of Kutools for Excel can help you quickly select entire rows or entire rows in the selection based on specific cell value in a certain column in Excel.  Download the full feature 60-day free trail of Kutools for Excel now!


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.


Kutools for Excel - Helps You Always Finish Work Ahead of Time, Have More Time to Enjoy Life
Do you often find yourself playing catch-up with work, lack of time to spend for yourself and family?  Kutools for Excel can help you to deal with 80% Excel puzzles and improve 80% work efficiency, give you more time to take care of family and enjoy life.
300 advanced tools for 1500 work scenarios, make your job so much easier than ever.
No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
Complicated and repeated operations can be done a one-time processing in seconds.
Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
Become an Excel expert in 3 minutes, help you quickly get recognized and a pay raise promotion.
110,000 highly effective people and 300+ world-renowned companies' choice.
Make your $39.0 worth more than $4000.0 training of others.
Full feature free trial 30-day. 60-Day Money Back Guarantee without reason.

Comments (98)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What would I need to add to this formula (Method A) to get a third BLANK option? And where would it go?
Example:
If the cell I'm searching is blank, I would like it to return no value in the other cell. I do not want the "if false" value to populate.
This comment was minimized by the moderator on the site
i want a cell to automatically choose a number based on the drop-down option in the cell before, e.g drop-down, half meal, full meal, if I select half meal drop-down, the next cell should show 50% while the full meal drop-down will reflect 100%. please help
This comment was minimized by the moderator on the site
Hi Josh,
In the cell where you want to output the percentage, enter the following formula and press the Enter key. Hope I can help.
=IF(C15="half meal", "50%", IF(C15="full meal", "100%", ""))
This comment was minimized by the moderator on the site
Is it possible to return a certain value in 1 column based on another?


What I currently have: On a differnt tab (same worksheet):
Column A Column B
Location Name Dept Location Name Location Code
Miami 4455 Miami 123


What I want to happen:
Location Name Dept
Miami 11234455

In Coumn B, I want it to first add a 1, then take what is in Column A, see what is the Location Code on then different tab, insert that location code, and add that to the front of the current data in Column B.

1 + 123 + 4455
This comment was minimized by the moderator on the site
Hi Jen Rader,
You can apply the following formula to get it done.
=IFERROR(1&VLOOKUP(A9,Sheet2!$A$2:$B$5,2, 0)&VLOOKUP(Sheet1!A9,Sheet1!$A$2:$B$5,2, 0),"")
In this formula,
1. A9 is the cell contains the value you want to return values based on;
2. Sheet2 is the tab name contains the "Location Code"; If the tab name contains space, please enclose the tab name in single quotes, such as 'tab name'.
3. Sheet1 is the tab name contains the "Dept";
4. $A$2:$B$5 is the range containing the table data (include both search values and return values ).
See screenshots below:
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/return1.png
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/return2.png
This comment was minimized by the moderator on the site
OMG Crystal! You are amazing! Show me your ways! Thank you so much - it worked exactly like I was hoping!
This comment was minimized by the moderator on the site
I am trying to find out the formula to copy the number shown in B2 on cell D2 if the Cell C2 has a letter "S". Can anyone help me?
This comment was minimized by the moderator on the site
Hi Aurora,
Please enter the following formula in cell D2.
=IF(ISNUMBER(SEARCH("S",C2)),B2,"No qualify")
This comment was minimized by the moderator on the site
Bonjour à tous, j'ai un tableau Excel et j'aimerais réaliser via une macro l'opération suivante: si une cellule contient une date, me renvoyer une valeur dans une autre cellule. Pourriez vous m'aider s'il vous plait? Je vous remercie par avance
This comment was minimized by the moderator on the site
STALLS D 25 Palais - Stalls SR
STALLS M 19 Palais - Stalls SR
LOUNGE E 22 Palais - Lounge SR
LOUNGE G 23 Palais - Lounge SL
ORCH K 40 Palais - Orchestra SR
For each line in a spreadsheet I need to evaluate against a table like above and find the value that matches all of the first 3 values.
Normally I would use sumifs for this type of problem but the answer is a text string so it returns 0
This comment was minimized by the moderator on the site
I need a formula that makes column N equal the text “OTO” if column K equals MCS TRAINING how do I do that???
This comment was minimized by the moderator on the site
Hi Abby,Please apply the following formula in the cells of column N:=IF(ISNUMBER(MATCH("MCS TRAINING",K1)),"OTO","NULL")
This comment was minimized by the moderator on the site
Hi - This is my formula that I am working with.  Currently it works, but where B21 is being returned, the value of B21 is a link to another place altogether.  I want to keep the display text but would like to include the link as well.  Can this be done?  =IF(ISNUMBER(SEARCH("DATA_MAPPING",general_report!AJ21)),general_report!B21,"")
This comment was minimized by the moderator on the site
Is it possible to split the return value to a different tab? IE: columns B and C are located on one tab, and values in columns E and F are located on a different tab?
This comment was minimized by the moderator on the site
Hi

Is it possible to populate another cell, not the cell with the formula.

Example
A1 Yes
A2 3
A3 Formula searches A1, Grabs value at A2 and if Yes Paste it in A4
A4 3 
This comment was minimized by the moderator on the site
Hi,
Sorry can't help you with the problem. I suggest you post the problem to the forum below to get help from other Excel enthusiasts.
https://www.extendoffice.com/forum/kutools-for-excel.html 
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations