Excel gurus - Formula help

[taz157]

I’m pretty good in Excel and can build some pretty intense formulas. I’m stumped at the moment. I want to build an OR formula that looks in a cell to see instances of Tesla, Escape or Ioniq in it; however, there’s no set place in which you’ll see the either word.

I had a formula written of ISNUMBER(SEARCH(“*escape”,A1)) and it returned TRUE. I tried to do an OR for the SEARCH part and I would get #N/A. Example of what I mean: ISNUMBER(OR(SEARCH(“*escape”,A1),SEARCH(“*tesla”,A1),SEARCH(“*ioniq”,A1))). The answer I get this way is #N/A even if 1 of those criteria is in the cell. I know there’s something I’m missing but not sure what.

Any ideas?

[saveinla]

Oct 3, 2022 18:54:57 GMT -5 taz157 said:

I’m pretty good in Excel and can build some pretty intense formulas. I’m stumped at the moment. I want to build an OR formula that looks in a cell to see instances of Tesla, Escape or Ioniq in it; however, there’s no set place in which you’ll see the either word.

I had a formula written of ISNUMBER(SEARCH(“*escape”,A1)) and it returned TRUE. I tried to do an OR for the SEARCH part and I would get #N/A. Example of what I mean: ISNUMBER(OR(SEARCH(“*escape”,A1),SEARCH(“*tesla”,A1),SEARCH(“*ioniq”,A1))). The answer I get this way is #N/A even if 1 of those criteria is in the cell. I know there’s something I’m missing but not sure what.

Any ideas?

Do you just need to know if they are in the cell? If so, I have used something like this before


=IF(SUM(COUNTIF(A1,{"*escape*","*tesla*"})),"TRUE","")

[saveinla]

BTW - this is the website that I used when I had to do something like that -

exceljet.net/formula/if-cell-contains-this-or-that

Maybe it will help if you can check that out.

[taz157]

Thanks saveinla! I didn’t know you could us {} in a formula too. This formula is part of a pretty big IFS formula but I think I can make it work. I really appreciate this!

[saveinla]

Oct 4, 2022 7:36:54 GMT -5 taz157 said:

Thanks saveinla ! I didn’t know you could us {} in a formula too. This formula is part of a pretty big IFS formula but I think I can make it work. I really appreciate this!

Happy to help - I am not an excel guru, but I just needed to do this recently and figured it could help

[taz157]

Oct 4, 2022 8:42:56 GMT -5 saveinla said:

Oct 4, 2022 7:36:54 GMT -5 taz157 said:

Thanks saveinla ! I didn’t know you could us {} in a formula too. This formula is part of a pretty big IFS formula but I think I can make it work. I really appreciate this!

Happy to help - I am not an excel guru, but I just needed to do this recently and figured it could help

It definitely helped!! I was able to incorporate it into my existing formula without any issues. 😁

[jerseygirl]

So taz seems you’re comparing EVs?
I have a Tesla and it’s very low maintenance and cool to drive!

[taz157]

Oct 4, 2022 10:05:35 GMT -5 jerseygirl said:

So taz seems you’re comparing EVs?
I have a Tesla and it’s very low maintenance and cool to drive!

Actually no I’m not. The depreciation is different for them than some other vehicles placed in service. I’m doing a book to tax comparison for work.