Excel if column contains value, then

Table of Contents Show

Return value in another cell if a cell contains certain text with formula
Return values in another cell if a cell contains different texts with formula
Easily return values in another cell if a cell contains different texts

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!

To test for cells that contain certain text, you can use a formula that uses the IF function together with the SEARCH and ISNUMBER functions. In the example shown, the formula in C5 is:

=IF(ISNUMBER(SEARCH("abc",B5)),B5,"")

To test only for "if cell equals" you don't need ISNUMBER or SEARCH.

=IF(ISNUMBER(SEARCH("abc",A1)),A1,"")

One limitation of the IF function is that it does not support wildcards like "?" and "*". This means you can't use IF by itself to test for text that may appear anywhere in a cell.

One solution is a formula that uses the IF function together with the SEARCH and ISNUMBER functions. In the example shown, we have a list of email addresses, and we want to extract those that contain "abc". In C5, the formula were using is this:

=IF(ISNUMBER(SEARCH("abc",B5)),B5,"")

If "abc" is found anywhere in cell B5, IF will return that value. If not, IF will return an empty string (""). In this formula, the logical test is this bit:

ISNUMBER(SEARCH("abc",B5))

This snippet will return TRUE if the the value in B5 contains "abc" and false if not. The logic of ISNUMBER + SEARCH is explained in detail here.

To copy cell the value in B5 when it contains "abc", we provide B5 again for the "value if true" argument. If FALSE, we supply an empty string ("") which will display as a blank cell on the worksheet.

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.