Return Value in Another Cell If Cell Contains Text in Google Sheets

When working with data in Google Sheets, you often need to take action based on whether a cell contains certain text. For example, if a status column contains the word “Shipped,” you might want to return the corresponding shipping date or mark it as “Complete.” Google Sheets doesn’t have a direct “IF CONTAINS” function, but you can achieve this using a combination of IF, SEARCH, or REGEXMATCH.

This article will show you how to check for specific text within a cell and return a value from another cell accordingly, perfect for project trackers, customer status sheets, or automated workflows.

When working with status fields that include additional text, such as “Shipped via FedEx” or “Already Shipped”, you might want to pull the Delivery Date only when the word “Shipped” appears anywhere in the status. Google Sheets doesn’t offer a direct “contains” function, but you can combine IF and SEARCH to achieve this.

This is the dataset we will use to demonstrate the methods.

Steps:

➤ Click on a blank cell next to your dataset (e.g., D2)
➤ Enter the formula:

➤ Press Enter and drag down the formula for all rows.

The formula checks if the text “Shipped” is present anywhere in the Status column (B). If found, it returns the corresponding Delivery Date from column C; if not, it returns a blank.

In cases where you want to return a value only when a cell matches a specific word exactly, such as returning the Delivery Date only if the Status is strictly “Shipped by DHL”, you can use a basic IF statement with a direct comparison. This method is useful when partial matches or variations like “Shipped via FedEx” should be excluded.

Steps:

➤ Click on a blank cell next to your dataset (e.g., D2)
➤ Enter the formula:

➤ Press Enter and fill down the column

This formula only returns the Delivery Date if the Status is exactly “Shipped by DHL”, and ignores variations like “Shipped via FedEx” and other cells containing the word.

When working with delivery status data in Google Sheets, you might need to identify rows that mention a keyword, like “Shipped”, regardless of the exact phrasing. Whether the cell says “Shipped via DHL” or “Order Shipped”, this method ensures you still capture those entries. Google Sheets’ REGEXMATCH function allows for flexible, partial matches using regular expressions, making it ideal for returning values such as Delivery Dates based on keyword presence.

Steps:

➤ Click on a blank cell next to your dataset (e.g., D2)
➤ Enter the formula:

➤ Press Enter and fill down the formula for all rows

This formula uses REGEXMATCH to check if the word “Shipped” appears anywhere in the Status text. It’s case-sensitive and flexible, ideal for capturing varied phrases while returning the correct Delivery Date.

Frequently Asked Questions

How do I check if a cell contains specific text in Google Sheets?

Use SEARCH(“text”, A1) or REGEXMATCH(A1, “text”) to detect if a word or phrase exists within a cell, even partially.

Can I return another cell’s value if text is found?

Yes. Use =IF(ISNUMBER(SEARCH(“keyword”, A1)), B1, “”) to check for the keyword and return the value from another cell if it’s found.

What if I want an exact match only?

For exact matches, use =IF(A1=”Shipped”, B1, “”). This returns another cell’s value only when the text matches exactly, including case and spacing.

Why is my formula not returning anything even when text exists?

This can happen due to case sensitivity, extra spaces, or unexpected characters. Try TRIM or use SEARCH for more flexible matching than exact equality.

Wrapping Up

Working with text-based conditions in Google Sheets can be simple yet powerful. Whether you’re checking for exact matches or partial phrases like “Shipped via DHL,” functions like IF, SEARCH, and EXACT let you return meaningful results, like a delivery date, based on text found in another cell. Choose your method based on how precise the match needs to be, and remember to test for formatting inconsistencies like extra spaces.