Excel Formula: If Cell Contains Text Then Return Value in Another Cell

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.

if cell contains text then return value in another cell using if function

  • 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.

if cell contains text then return value in another cell employing ISNUMBER and FIND functions

  • 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.

if cell contains text then return value in another cell utilizing ISNUMBER and SEARCH functions

  • 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.

if cell contains text then return value in another cell implementing IF and OR functions

  • 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.

if cell contains text then return value in another cell using IF and AND functions

  • 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.

if cell contains text then return value in another cell applying INDEX and MATCH functions

  • 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.

if cell contains text then return value in another cell employing VLOOKUP function

  • 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.


Related Articles

(Visited 103 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo