200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Help! What is wrong with my formula???
=IF(ISNUMBER(SEARCH("aft",A1)),24,C1)
So I have $50 in C1 and A1 says aft 234 but it keeps showing 50 instead of 24
I'm looking for a formula that says if the cell contains "aft" at all then it's 24 but if it doesn't then it's the original number that is entered in a different cell (C1) (I got that part down cause 50 keeps showing up). Why does 24 not show up when A1 says "aft 234", it contains aft! Can't figure it out🤨!
Thanks!
Ann
Ango
Your formula should do what you describe (it does for me) so I can only think of two possibilities:
1) The Microsoft guidance on SEARCH, SEARCHB functions says "These functions may not be available in all languages." (but it certainly works in English). I tested that by changing SEARCH to SEARCH2 (which doesn't exist) and with that unknown function, the formula behaves as you say.
2) If you don't need a case-insensitive search (e.g. if the cells will always contain "aft" rather than "Aft") then you could try:
=IFERROR(IF(FIND("aft",A1)>0,24), C1)Hope this helps.