asked on

# Excel formula using OR, acting on two params not performing as required.

Using this :

`=IF(OR(FIND("=2023",C5)>=0,FIND("=2024",C5)>=0),1,2)`

returns a #VALUE fault . . .

whereas this :

`=IF(FIND("=2023",C5)>0,1,2)`

works fine. Can anyone help the first statement with two params, get working please?

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

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

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)

ASKER

BTW that is looking for the whole stringYes, 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.

ASKER

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

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

ASKER

=IF(COUNT(FIND({"=2023","=

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.

ASKER

Thanks again.

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.

ASKER

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.

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

ASKER

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: 65905a128db06ba48db9472428

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.

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. ;)

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)