Excel Formula to Retrieve Value Based on Cell Content

Overview

This document provides a clear formula to retrieve text from one cell based on whether another cell contains specific content. This is commonly used to perform lookups or conditional retrievals.

Formula

The formula utilizes the IF and SEARCH functions to evaluate the conditions. The general syntax is as follows:

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

Components Explained

• SEARCH("keyword", A1): This function searches for the specified "keyword" within the text of cell A1. If found, it returns the position of the keyword; if not found, it results in an error.
• ISNUMBER(...): This function checks whether the result of SEARCH is a number (indicating that the keyword was found). If it is a number, it returns TRUE; otherwise, it returns FALSE.
• B1: This is the cell from which text will be retrieved if the condition is met (i.e., if the keyword is found in A1).
• "": This empty string is returned if the keyword is not found.

Practical Example

Scenario

Assume you have a dataset where:

• Column A contains product descriptions.
• Column B contains the corresponding product codes.

You want to retrieve the product code in Column B if the description in Column A contains the word "Apple".

Example Data

Implementation

To find the product code for "Apple" in cell A1 and return the corresponding code from B1:

In cell C1, enter the following formula:

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

Expected Output

After applying the formula from C1 down to C4, the results will be as follows:

Conclusion

This formula provides a straightforward method to conditionally retrieve data based on the presence of a specific substring in another cell. Users can apply this approach in various datasets to enhance their data management capabilities in Excel. For further learning on advanced Excel formulas and functionalities, consider exploring the courses available on the Enterprise DNA Platform.