Because FIND 2024 throws an error, it doesn't return TRUE or FALSE.

Try:

=IF(OR(NOT(ISERROR(FIND("=2023",C5))),NOT(ISERROR(FIND("=2024",C5)))),1,2)

Try it like this:

=IF(OR(ISNUMBER(FIND({"=2023","=2024"},C5))),1,2)

Select allOpen in new window

BTW that is looking for the whole string "=2023" or "=2024". Is that what you need?

The trouble you were running into is the fact that FIND returns an error value when the text it is looking for isn't present. OR sees the error value and returns #VALUE! as a consequence.

By wrapping FIND in either ISNUMBER or IFERROR, you allow the OR to work.
OR-with-FIND.xlsx

Alternatively, since COUNT ignores errors, you could use:

=IF(COUNT(FIND({"=2023","=2024"},C5)),1,2)

BTW that is looking for the whole string

Yes, that's what I'm after.

So Rob, your two versions return "1", which is what I want.

byundt and Rory - your forumulae return either "1" or "2", depending on what's in the source cell . . . so not quite what I was looking for but thanks for the comments and the option of doing it that way if such a future need arises.

They all return the same values.

If the formulas are not working the way you want, please post a description of what you want. A sample workbook showing test data and desired results will get you a good answer on the first post in many cases.

If cell C5 contains this :

=1998.28/12&T("=2024")

then the formula cell will return "2".

If cell C5 contains this :

=1998.28/12&T("=2023")

the formula cell will return "1".

I need "1" to be returned whether it's "=2023" or "=2024". Only Rob Henson's formulae do that.

Actually, all of the suggested formulas work exactly the way you requested in your last Comment.

It can be tricky to see the curly braces { } used in my Rory's formula and mine. Had you copied the formula and pasted in your workbook, you would have found that both of them worked. I also posted a sample workbook so you could see the formula working at your end.


OR-with-FIND.xlsx

I did indeed copy and paste the formulas of all the alternatives, and I just tried it again for this one :

=IF(COUNT(FIND({"=2023","=2024"},C5)),1,2)

It returns "2", even if "=2024" is in C5.  

Need to confirm entry with Carl & Shift & Enter rather than just Enter. The formula will then change and get curly brackets at each end

My formula does not require array entry (I can’t recall what Brad’s was but will check later). I’d like to see a workbook where my formula doesn’t work, if possible.

The curly braces are present in the comment I posted above, and in the formula in the worksheet. I can only repeat what I see on the screen of the worksheet into which I enter the formula . . . and the result is that it returns a 2 as mentioned, which is the result I'm not so interested in, whereas Rob Henson's returns the 1 need. I'm happy with that as my solution here.

Thanks again.

Both my formula and Rory's work just fine as regular formulas. And as you can see from the screenshot (taken in Excel 2013), the formulas in rows 5 through 7 return 1 for test strings that contain either =2023 or =2024, and 2 if the test string contains neither.

The test strings you posted are also a valid Excel formula. When I allowed the test data formulas to evaluate, the suggested formulas that were supposed to return 1 or 2 still worked as intended. See rows 9 to 11 in the screenshot below.

Rory, Rob and I have each answered thousands of Excel questions on this forum, and we test our suggestions before posting. The fact that you are getting different results than we are seeing tells me there must be something not yet mentioned regarding your data or testing of the suggestions. If so, it may be that a different approach than any of us has suggested will be needed to cover all possible edge cases.

Rory, Rob and I have each answered thousands of Excel questions on this forum, and we test our suggestions before posting.

Great. I used to programme turnkey spreadsheets in Lotus 1-2-3, Improv and Excel for many years. Of course I also tried to test what I did. But that was 30 years ago, or more. But perhaps the point you are missing on, is that I have a source cell which has both 2023 and 2024 at the same time. That was why, in my original post, the OR function is used. It looks to me like only Rob got that jist . . . I don't know . . . but you can see on this screenshot the results I have coming out of my machine.

Which version of Excel is that? Your screenshot doesn't look like any one I recognise.

Which version of Excel are you using?
Is the calculation mode perhaps Manual?
When you open the file I posted, does it produce different results than I showed in my screenshot?


OR-with-FIND--Excel-2013.xlsx

Which version of Excel is that?

Which version of Excel are you using?

It's a LibreOffice jobbie, saved in .ods file format. The Help version tag is :

Version: 6.1.2.1 (x64)
Build ID: 65905a128db06ba48db947242809d14d3f9a93fe
CPU threads: 4; OS: Windows 6.3; UI render: default;
Locale: en-GB (en_GB); Calc: CL

Is the calculation mode perhaps Manual?

No. (And if by chance it is, I recalculated several times).

When you open the file I posted, does it produce different results than I showed in my screenshot?

No. Results are the same.

Krakatoa,
Thank you very much for bearing with me in the issue of why the suggested formulas don't work.

I lay the ultimate responsibility at the feet of the LibreOffice developers, because they aren't 100% compatible with Excel. Specifically, they aren't performing array-lifting.

In such a situation, you are quite correct to use Mr. Henson's formula.

Brad Yundt

It would probably make sense to ask for a LibreOffice solution rather than an Excel one though in future. ;)