If a cell has text, we can return a corresponding value to another cell using different formulas in Excel. This is a very handy operation when we work with a large dataset. In this article, we will discuss 7 approaches if a cell contains text then return value in another cell using the Excel formula.

## 📁 Download Excel File

Download the following Excel workbook to realize the topic more clearly.

## Learn to Apply Excel Formula If Cell Contains Text Then Return Value in Another Cell with These 7 Examples

Today we will show how to get value in a different cell if a cell contains text with the following dataset that contains Code, Items, Size, and Price of different desserts.

**Example**

### 1. Applying IF Function

In the first example, we will use **the IF function **to get value in another cell. The **IF** function is not case-sensitive.

⬇️⬇️ **STEPS **⬇️⬇️

- Here, we want to show
**Available**if the**Size**is**Regular**otherwise the cell will show**Not Available**. - First, select cell
**E6**and type in the following formula, and press**Enter**:

`=IF(D6="Regular","Available","Not Available")`

**🔨 Formula Breakdown**

**IF(D6=”Regular”,”Available”,”Not Available”)**

👉 If cell **D6 **contains **Regular**, cell **E6 **will show **Available **otherwise cell **E6 **will show **Not Available**.

- Later, use the
**Fill Handle Icon**from the bottom right corner of cell**E6**and**double-click**.

- Momentarily, we will see the formula is applied to the whole
**Availability**column. - From the screenshot below, we can see if the
**Size**is**Regular**, the cells are showing**Available**.

**📕 Read More: 8 Ways to Return Value If Cell Contains Text in Excel**

**Example**

### 2. Merging ISNUMBER and FIND Functions

Now, we will merge the **ISNUMBER **and **FIND **functions to return the value in a different cell.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, select cell
**E6**and type in the formula below, and press**Enter**:

`=ISNUMBER(FIND("Apple Pie",$C$6:$C$11))`

**🔨 Formula Breakdown**

**ISNUMBER(FIND(“Apple Pie”,$C$6:$C$11))**

👉 First of all, let’s see what **the FIND function **does. The **FIND **function will search **Apple Pie **through cells **C5 **to **C11**.

👉 Note that the **FIND **function is case-sensitive.

👉 Now, **the ISNUMBER function** will return **TRUE **if it contains **Apple Pie **otherwise it will return **FALSE**.

- Afterward, use the
**Fill Handle Icon**to apply the formula. - We can see from the screenshot below that the cells containing
**Apple Pie**are showing**TRUE**in the**Availability**column.

**📕 Read More: ****If Cell Contains Text From List Then Return Value in Excel**

**Example**

### 3. Combining ISNUMBER and SEARCH Functions

Now it’s time to combine the **ISNUMBER **and **SEARCH **functions to return value in another cell. The **SEARCH **function is not case-sensitive.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, select cell
**E6**and write the following formula, and press**Enter**:

`=ISNUMBER(SEARCH("Apple Pie",$C$6:$C$11))`

**🔨 Formula Breakdown**

**ISNUMBER(SEARCH(“Apple Pie”,$C$6:$C$11))**

👉 First, the** SEARCH **function will search **Apple Pie **through cells **C5 **to **C11**.

👉 Now, the** ISNUMBER **function will return **TRUE **if the cell contains **Apple Pie **otherwise it will return **FALSE**.

** **

- Next, use the
**Fill Handle Icon**. - Notice that cells
**C8**and**C10**contain**Apple pie**and the**Availability**column is showing**TRUE**even if the cells have upper or lower cases.

** **

**Example**

### 4. Utilizing IF and OR Functions

Let’s see how to utilize the **IF **and **OR **functions together to return value in another cell when the cell contains text.

⬇️⬇️ **STEPS **⬇️⬇️

- Now, select cell
**E6**and write the following formula and press**Enter**:

`=IF(OR(ISNUMBER(SEARCH("baked",C6)),ISNUMBER(SEARCH("pie",C6))),"Available","Not Available")`

**🔨 Formula Breakdown**

**IF(OR(ISNUMBER(SEARCH(“baked”,C6)),ISNUMBER(SEARCH(“pie”,C6))),”Available”,”Not Available”)**

👉 The **ISNUMBER **function will return **TRUE **if the **SEARCH **function finds **baked **or **pie **in cell **C6**.

👉 The** OR **function will return **TRUE **if either **baked **or **pie **is present in cell **C6**.

👉 Now, the **IF **function will return **Available **if the **OR **function returns **TRUE**.

- At this time, use the
**Fill Handle Icon**to apply the formula to the**Availability**column. - After, we will find that the cells containing
**pie**or**baked**will show**Available**in the**Availability**column.

**Example**

### 5. Integrating IF and AND Functions

In our fifth approach, we will integrate the **IF **and **AND **functions to return value in another cell. It is similar to the **IF **and **OR **functions.

⬇️⬇️ **STEPS **⬇️⬇️

- Here, we will enter the following formula in the cell
**E6**and press**Enter**:

`=IF(AND(ISNUMBER(SEARCH("baked",C6)),ISNUMBER(SEARCH("pie",C6))),"Available","Not Available")`

**🔨 Formula Breakdown**

**IF(AND(ISNUMBER(SEARCH(“baked”,C6)),ISNUMBER(SEARCH(“pie”,C6))),”Available”,”Not Available”)**

👉 **The ISNUMBER function** will return **TRUE **if the **SEARCH **function finds **baked **or **pie **in cell **C6 **otherwise it will return **FALSE**.

**👉 The AND function **will return **TRUE **if both **baked **and **pie **are present in cell **C6**.

👉 Now, the **IF **function will return **Available **if the **AND **function returns **TRUE **else it will return** Not Available**.

- It is time to use the
**Fill Handle Icon**. - After that, just like in the photo below, we will see
**Available**in the**Availability**column only for the**Baked apple pie**. Because cell**C10**is the only cell that contains both**baked**and**pie**.

**Example**

### 6. Coalescing INDEX and MATCH Functions

Let’s learn how to coalesce the **INDEX **and **MATCH **functions.

⬇️⬇️ **STEPS **⬇️⬇️

- First, select cell
**C14**and type in the following formula, and press**Enter**:

`=INDEX(E6:E11,MATCH("M11",B6:B11,0))`

**🔨 Formula Breakdown**

**INDEX(E6:E11,MATCH(“M11”,B6:B11,0))**

**👉 **The** MATCH** function will find **M11 **through cells **B6 **and **B11**. **0 **means, we want an exact match.

👉 The** INDEX **function will find the corresponding **Price **from cells **E6 **to **E11**.

- From the above screenshot, we can see that the
**Price**of the**Code M11**is**2.75**which is shown in cell**C14**.

**Example**

### 7. Implementing VLOOKUP Function

Now it’s time to implement **the VLOOKUP function **to return value in another cell if a cell contains text.

⬇️⬇️ **STEPS **⬇️⬇️

- First, select cell
**C14**and type in the following formula, and press**Enter**:

`=VLOOKUP(B14,B6:E11,2,FALSE)`

**🔨 Formula Breakdown**

**VLOOKUP(B14,B6:E11,2,FALSE)**

**👉 The VLOOKUP function** will find the value present in cell **B14 **which is **M11 **through cells **B6 **and **B11**. **FALSE **means, we want an exact match.

👉 Next, the **VLOOKUP **function will find the corresponding **Item **from the **2nd **column which is **Items**.

** **

- From the above photo, we can see that the Item of
**Code M11**is**Blueberry Muffin**which is shown in cell**C14**.

**📕 Read More: VLOOKUP If Cell Contains a Word Within Text in Excel**

## 📝 Takeaways from This Article

📌 Firstly, we learned how to return value in a different cell if a cell contains text using the **IF **function.

📌 Secondly, we merged the **ISNUMBER **and **FIND **functions to return TRUE or FALSE.

📌 Thirdly, we combined the **ISNUMBER **and **SEARCH **functions to return value in another cell

📌 Then, we returned the value in a different cell using the **IF **and **OR **functions.

📌 Later, we used the **IF **and **AND **functions.

📌 Next, with the help of the **INDEX **and **MATCH **functions, we returned the corresponding value of one cell to another.

📌 Finally, we implemented the **VLOOKUP **function.

## Conclusion

That concludes the discussion for today. These are some convenient methods to return value in another cell if a cell contains text using the Excel formula. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website **ExcelDen** to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.