How to return value in another cell if a cell contains certain text in Excel?
As the example shown as below, when cell E6 contains value “Yes”, cell F6 will be automatically populated with value “approve”. If you have changed the “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
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:
Bring Tabbed Editing and Browsing in Office
--- Make Your Work Much Easier
Office Tab will save 50% of your work time when browsing & editing multiple documents.
Unbelievable! The operation of two or more documents is even pleasant than the single document operation.
Reduce thousands of keyboard & mouse operations every day, farewell to occupational diseases now.
The interface of Office Tab is far more powerful and efficient than internet browsers.
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
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 the cell you will automatically return values based on specific cell value, click Kutools > Formula Helper > Look for a value in list. See screenshot:
3. Then the Formula Helper dialog box pops up:
- 3.1 In the Table_array box, select the table without headers you have created in step 1;
- 3.2 In the Look_value box, select the cell contains the specific value you will return value based on;
- 3.3 In the Column box, select the column range (without header) that contains the values you will auto return;
- 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:
Tip. If you want to have a free trial (60-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.