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.
Use formula to return values in another cell if a cell contains different texts
Apply several clicks to easily return values in another cell if a cell contains different texts
Use formula to return value in another cell if a cell contains certain text
To return a value in another cell if a cell contains specific text, 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",B5)),"Approve","No qualify")Notes:
1. In the formula, "Yes", B5, "approve" and "No qualify" indicate that if cell B5 contains the text "Yes", the specified cell will be populated with the 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",B5)),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.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Use formula to return values in another cell if a cell contains different texts
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.
Apply several clicks to 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:
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in