Function Problems

How to Return a Value from Another Column Based on a Text Match in Excel

Huma Ashraf Ch Sep 27, 2026 869 views

Question details

The user needs to extract a corresponding value from Column D and display it in Column F, but only if the adjacent cell in Column E contains the text "OT". If it does not contain "OT", the cell should remain blank.

Return a Value from Another Column When a Cell Contains Specific Text

Product: Excel
Device & OS: not provided
Scenario: Calculating overtime hours by checking for a specific text string within a cell and pulling data from a related numerical column.
Observed behavior: Requires a functional formula to conditionally pull data based on a partial text match without triggering #VALUE! errors when the text is missing.

Before you start

Ensure that your data is properly aligned in adjacent columns and that Column D contains the correct numerical values you wish to return. Ensure there are no merged cells in your working range.

Solution 1Recommended

Use IF, ISNUMBER, and SEARCH Functions

This is the most reliable method to check for a partial text match anywhere within a cell and return a corresponding value while safely handling missing text.

The SEARCH function looks for the text "OT" within the specified cell. Because SEARCH returns an error if the text is not found, wrapping it in ISNUMBER converts the result into a clean TRUE or FALSE, which the IF function uses to either return the value from Column D or leave the cell blank.

Select the target cell

Click on cell F2 (or the first cell in the column where you want the overtime calculation to appear).

Enter the formula

Type the following formula: =IF(ISNUMBER(SEARCH("OT",E2)),D2,"")

Apply the calculation

Press the Enter key to view the result for the first row.

Fill down the column

Click and drag the small square (fill handle) at the bottom-right corner of cell F2 down to apply the formula to the rest of your dataset.

Case Insensitivity: The SEARCH function is case-insensitive. If you need a strict case-sensitive match (e.g., only matching uppercase "OT" and ignoring "ot"), replace SEARCH with the FIND function: =IF(ISNUMBER(FIND("OT",E2)),D2,"").

Solution 2

Use IF, IFERROR, and SEARCH Formula

An alternative approach that uses IFERROR to catch the error generated by SEARCH when the specific text is missing.

Calculate Overtime and Text Matches Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical formulas including IF, ISNUMBER, and SEARCH. You can seamlessly calculate overtime hours and manipulate text data without changing your existing workflow.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the overtime data.
2. Select the destination cell: Click on the cell in Column F where you want the first result to appear.
3. Input the formula: Enter =IF(ISNUMBER(SEARCH("OT",E2)),D2,"") exactly as you would in standard spreadsheet software.
4. Fill the formula down: Press Enter, then double-click the fill handle in the bottom right corner of the cell to instantly apply it to the entire column.

100% compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Lightweight performance ensures fast calculations even on large data sets.Familiar user interface means zero learning curve for Excel users.Free to use for everyday data analysis and spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

How can I return a value if the cell exactly matches 'OT' instead of just containing it?

If you only want to return a value when Column E contains exactly 'OT' and nothing else, you don't need SEARCH. Simply use an exact match formula: =IF(E2="OT",D2,"").

Why is my SEARCH formula returning a #VALUE! error?

The SEARCH function naturally returns a #VALUE! error if it cannot find the specified text. When nested inside a standard IF statement without ISNUMBER or IFERROR to handle the error, the entire formula will output #VALUE! instead of leaving the cell blank.

Can I check for multiple different text strings at the same time?

Yes. You can nest multiple IF statements or use the IFS function combined with ISNUMBER(SEARCH()). Alternatively, you can add logical checks together, such as =IF(OR(ISNUMBER(SEARCH("OT",E2)), ISNUMBER(SEARCH("OVERTIME",E2))), D2, "").

Excel & Data Problems