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

We often need to insert values into a cell based on other cells in Google Sheets. Also, finding information in a large spreadsheet takes a lot of work. However, you can find information based on values in another cell. In this article, I’ll discuss 7 helpful methods to form formulas in Google Sheets if a cell contains specific text and then return value in another cell.

## A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following link. The spreadsheets contain an overview of the datasheet and an outline of the described methods to form formulas in Google Sheets if the cell contains specific text and then return the value in another cell.

## 7 Methods to Return Value in Another Cell If Cell Contains Text in Google Sheets

First, let’s get familiar with our datasheet. It contains the order number, product name, and payment status for several customer orders. We’ll set the order status as “Confirm” or “Pending” based on subsequent payment status.

### 1. Using IF Function

We can simply use the IF function to test whether the payment status is “Paid” or “Due” and return values in another cell accordingly.

Steps:

• First, select Cell E5 and then type in the following formula-
`=IF(D5="Paid","Confirm","Pending")`
• After that, press Enter key to get the required value.

• Point your mouse to the lower right corner of Cell E5 to view the Fill Handle Drag the Fill Handle icon up to Cell E14 to get values for other cells of Column E.

• Thus we can return values in cells of Column E based on text values in cells of Column D.

### 2. Merging IF and REGEXMAX Functions

We can merge the REGEXMAX function with the IF function to perform the logical test and then return values in a cell if another cell contains a specific test.

Steps:

• Select Cell E5 first and then type in the following formula-
`=IF(REGEXMATCH(D5,"Paid"),"Confirm","Pending")`
• After that, press Enter key to get the required text value.

Formula Breakdown

• REGEXMATCH(D5,”Paid”)

First, the REGEXMATCH function checks whether Cell D5 contains the string “Paid” and then returns TRUE if a match is found. Else, it returns FALSE.

• IF(REGEXMATCH(D5,”Paid”),”Confirm”,”Pending”)

Finally, the IF function returns “Confirm” if a match is found in the logical test. Else, it returns “Pending”.

• Finally, use the Fill Handle icon to fill values in other cells of Column E.

### 3. Uniting IF, ISNUMBER, and SEARCH Functions

Another way to perform the logical test in the IF function is to unite it with the ISNUMBER and SEARCH functions.

Steps:

• First, select Cell E5 and type in the following formula-
`=IF(ISNUMBER(SEARCH("Paid",D5)),"Confirm","Pending")`
• Afterward, press the Enter key to get the required text.

Formula Breakdown

• SEARCH(“Paid”,D5)

First, the SEARCH function searches for the string “Paid” in Cell D5. If the string is found, it returns the position (a number) where the string is in Cell D5. Else, it returns #Value! error which is a text.

• ISNUMBER(SEARCH(“Paid”,D5))

Afterward, if the SEARCH function finds a match and returns a number, the ISNUMBER function returns TRUE. Else, it returns FALSE.

• IF(ISNUMBER(SEARCH(“Paid”,D5)),”Confirm”,”Pending”)

Finally, the IF function returns “Confirm” if the logical expression is true. Else, it returns “Pending”.

• Finally, use the Fill Handle icon to get values in other cells of Column E.

### 4. Joining IF and OR Functions

If you want to return values based on text values in multiple cells, you can use the OR function and the AND function. The OR and AND functions can be applied alongside any of the previous three methods.

Here, I’ll be joining the IF and OR functions. I have made some changes to the datasheet too. A new column titled Stock is added. I’ll cancel the order if a product is out of stock or the payment is due. Otherwise, I’ll confirm the order.

Steps:

• First, select Cell F5.
• After that, type in the following formula-
`=IF(OR(D5="Out of Stock",E5="Due"),"Cancel","Confirm")`
• Press the Enter key to get your required result.

Formula Breakdown

• OR(D5=”Out of Stock”,E5=”Due”)

First, the OR function checks the criteria at Cell D5 and E5. If none of the criteria matches, then the OR function returns FALSE. Otherwise, it returns TRUE.

• IF(OR(D5=”Out of Stock”,E5=”Due”),”Cancel”,”Confirm”)

After that, the IF function returns “Cancel” if the logical expression is true. Else, it returns “Confirm”.

• Finally, use the Fill Handle icon to get values in other cells of Column F.

### 5. Combining IF and AND Functions

Combining the AND function with the IF function is very similar to the joining of the OR and IF functions. Although, unlike the OR function, the AND function returns TRUE when both criteria match. So, there will be a slight change from the previous method in the formation of the formula.

Steps:

• First, select Cell F5.
• After that, type in the following formula-
`=IF(AND(D5="Available",E5="Paid"),"Confirm","Cancel")`
• Press the Enter key to get your required result.

Formula Breakdown

• AND(D5=”Available”,E5=”Paid”)

To start with, the AND function checks the criteria at Cell D5 and E5. If both the criteria match, the AND function return TRUE. Else, it returns FALSE.

• IF(AND(D5=”Available”,E5=”Paid”),”Confirm”,”Cancel”)

Finally, the IF function return “Confirm” if the logical test is true, else it returns “Cancel”.

• Finally, use the Fill Handle icon to fill other cells in Column F.

### 6. Joining IFERROR and VLOOKUP Functions

If you simply want to search values in a datasheet based on specific values from a cell, you can use the VLOOKUP function.  For example, we want to extract the product name for any order number. Change the dataset to the following for that. As we can see, one cell contains the order number in Cell B17. Now, we will return the product name in Cell C17 using the VLOOKUP function.

Steps:

• First, select Cell C17.
• After that, type in the following formula:
`=IFERROR(VLOOKUP(B17,B5:E14,2,False),"Incorrect Entry")`
• Finally, press Enter key to get the required text value.

Formula Breakdown

• VLOOKUP(B17,B5:E14,2,False)

The VLOOKUP function searches down the first column of the range B5:E14 for the search key specified by Cell B17 and returns the value from column 2 of the range in the row where a match is found.

• IFERROR(VLOOKUP(B17,B5:E14,2,False),”Incorrect Entry”)

If the VLOOKUP function doesn’t find a match, then the IFERROR function returns the string “Incorrect Entry”.

### 7. Combining INDEX and MATCH Functions

An alternative to the VLOOKUP function is to combine the INDEX and MATCH functions. We’ll also use the IFERROR function in case incorrect entries are made.

Steps:

• Select Cell C17.
• Then, type in the following formula:
`=IFERROR(INDEX(C5:C14,MATCH(B17,B5:B14,0)),"Incorrect Entry")`
• Press Enter key finally to get the required result.

Formula Breakdown

• MATCH(B17,B5:B14,0)

First, the MATCH function returns the relative position of the search key specified by Cell B17 in the range B5:B14.

• INDEX(C5:C14,MATCH(B17,B5:B14,0))

Then, the INDEX function returns the content of the cell in the relative position returned by the MATCH function from range C5:C14.

• IFERROR(INDEX(C5:C14,MATCH(B17,B5:B14,0)),”Incorrect Entry”)

Finally, if any error occurs in the INDEX and MATCH functions, the IFERROR function returns the string “Incorrect Entry”.

## Things to Be Considered

• The VLOOKUP function is not case-sensitive.
• If the dataset is not sorted while using the VLOOKUP function, then let Google Sheets know about it by specifying the fourth argument as False or 0.

## Conclusion

This concludes our article on how to form a formula in Google Sheets if a cell contains text and then return a value in another cell. I hope the described methods were sufficient for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more helpful articles.

## Related Articles

#### Seemanto Saha

Hello, I am Seemanto Saha, and working as a Technical Writer and Content Creator at OfficeWheel.com. I completed my B.Sc. in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. Being an enthusiast for solving analytical problems, I aim to leverage my research and analytical skills to create better content for everyone.

We will be happy to hear your thoughts