How to Find Text and Return Another Cell's Value in Excel
Question details
The user needs a formula to search a specific column for a keyword (like 'Total') and return the value from an adjacent cell when a match is found, or leave the cell blank if no match exists.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting specific corresponding values from a dataset based on a partial text match within a target column.
- Observed behavior
- The desired goal is to output the corresponding value automatically upon a successful text match, while returning an empty string for unmatched rows.
Identify the exact column containing the text you want to search and the column containing the values you wish to return before writing your formula.
Use the IF, ISNUMBER, and SEARCH Formula Combination
Combine the IF, ISNUMBER, and SEARCH functions to look for a specific substring and dynamically pull a value from another cell.
The SEARCH function looks for the target text (e.g., 'Total') within a referenced cell. If it finds the text, it returns its starting position as a number; otherwise, it returns an error. The ISNUMBER function converts this numeric position into TRUE and the error into FALSE. Finally, the IF function evaluates this TRUE/FALSE result, outputting your target cell's value if TRUE, or a blank cell if FALSE.
Click on the empty cell where you want the extracted value to appear.
Type the formula: =IF(ISNUMBER(SEARCH("Total", G1)), H1, ""). Replace 'G1' with the cell you are searching in, and 'H1' with the cell containing the value you want to return.
Press Enter to get the result. Click the result cell again, grab the small square at the bottom-right corner (fill handle), and drag it down to apply the formula to the remaining rows.
Extract and Lookup Data Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical and lookup formulas like IF, ISNUMBER, and SEARCH. You can easily process complex datasets, perform partial text lookups, and automate your data extraction securely.
- 1. Open your workbook: Launch WPS Office and open your spreadsheet document.
- 2. Select the target cell: Click on the cell where the lookup result should be displayed.
- 3. Input the formula: Use the built-in formula helper or manually type =IF(ISNUMBER(SEARCH("Total", G1)), H1, "") and press Enter.
- 4. Auto-fill the rest: Double-click the fill handle in the bottom-right corner of the cell to instantly apply the formula to the rest of your data.
Frequently Asked Questions
How can I return a custom message instead of a blank cell?
You can change the last part of the IF formula. Instead of using empty quotation marks (" "), insert your desired text. For example: =IF(ISNUMBER(SEARCH("Total", G1)), H1, "Not Found").
Can I search for multiple different words at the same time?
Yes. You can nest multiple SEARCH functions inside an OR function. The formula would look like this: =IF(OR(ISNUMBER(SEARCH("Word1", G1)), ISNUMBER(SEARCH("Word2", G1))), H1, ""). This returns H1 if either Word1 or Word2 is found.
Why is the formula returning a #VALUE! error?
A #VALUE! error typically occurs if the cell references are incorrect or if there is a data type mismatch. Ensure that the text you are searching for is enclosed in double quotation marks and that your column references (like G1 and H1) match the actual data layout.
