Excel Formula to Retrieve Cell Text Based on Condition

Objective

To create an Excel formula that retrieves the text from one cell if another cell contains a specified value.

Formula

The formula can be constructed using the IF function combined with the SEARCH function. The syntax is as follows:

=IF(ISNUMBER(SEARCH("keyword", A1)), B1, "")

Explanation

1. SEARCH("keyword", A1):

   • This function checks if the word "keyword" exists in cell A1.
   • It returns the position of the first character of the first instance of "keyword" if found; otherwise, it results in an error.

2. ISNUMBER(...):

   • This wraps around the SEARCH function to check if a number was returned (meaning the keyword was found).
   • It returns TRUE if a number (position) is returned, and FALSE if an error occurs.

3. IF(...):

   • This function will return the value in B1 if the condition provided by ISNUMBER is TRUE (i.e., the keyword was found).
   • If FALSE, it will return an empty string ("").

Practical Example

Scenario

You have a list of employee comments in column A and would like to display the corresponding department name from column B if the comment contains the word "urgent".

Data Setup

Implementation

In cell C1, you would enter the following formula:

=IF(ISNUMBER(SEARCH("urgent", A1)), B1, "")

Dragging the Formula

After entering the formula in C1, drag it down to fill other cells in column C to apply it to other comments.

Result in Column C

Conclusion

This formula allows users to conditionally retrieve text from another cell based on the presence of a keyword, enhancing data analysis and organization in Excel. For further refinement of Excel skills, consider exploring resources available on the Enterprise DNA Platform.