Nazmus Sakib
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.
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
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
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.
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.
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.
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.
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.
