Boolean operator within excel function

I am not new to excel or vba; however, I am having some difficulty with the lack of a boolean "or" function in excel. As you know, the "&" function works fine, but you cannot insert the "|" or "||" operator into a formula. Is there an alternative that will give me an "or"?

Note: The pre-defined "or function" (i.e., [or(condition1, condition 2...)] is not helpful in this instance. It evaluates values in another set of cells rather than the current cell and returns "true" or "false" instead of a numeric value.

The code that I am currently using is the following: =IF(IFERROR(INDEX($H$2:INDIRECT("H"&ROW()-1),MATCH(7,$D$2:INDIRECT("D"&ROW()-1),0)),0)=99,(INDIRECT("F"&ROW())*2),INDIRECT("F"&ROW()))

The value "7" is currently being matched. I need it to match multiple values, such as Match(5|6|7,... or Match (5||6||7,...

Any bright ideas?


microsoft-excel
shareeditflag

asked 1 min ago
Noah
1

As I mentioned in my thread, that is not appropriate to this task. It requires a conditional value from another cell and returns only a true or false rather than a number.

Before diving into this...
Can you confirm that the formula you posted actually provides the desired results based on 7 in the match?

And just to make this easier, lets take the indirect out of it. Much easier to read.
Assuming the formula is entered in say row 21, so ROW()=21
That formula would translate to

=IF(IFERROR(INDEX($H$2:$H$20,MATCH(7,$D$2:$D$20,0)),0)=99,(F21*2),F21)

And what you want is to do the INDEX with 5, 6, and 7 in the Match, and if any of them = 99 then... etc.
sort of like
If INDEX($H$2:$H$20,MATCH(5,$D$2:$D$20,0)),0)=99 OR INDEX($H$2:$H$20,MATCH(6,$D$2:$D$20,0)),0)=99 OR INDEX($H$2:$H$20,MATCH(7,$D$2:$D$20,0)),0)=99 Then...

Is that about right?

Yes, that is a long way of doing it, but that is exactly what I want it to do. Will the OR function work like that? I will give it a shot and see.

No, you're right the OR won't work for this.

Try
=IF(SUM(COUNTIFS($D$2:$D$20,{5,6,7},$H$2:$H$20,99)),F21*2,F21)

I'll leave it to you to put the indirect back into it.

Yes, that is a long way of doing it, but that is exactly what I want it to do. Will the OR function work like that? I will give it a shot and see.

>>I just tried that and again, OR is looking for conditional values in a specified other location and wants to spit out trues and falses.

Or even

=IF(COUNTIFS($D$2:$D$20,">=5",$D$2:$D$20,"<=7",$H$2:$H$20,99),F21*2,F21)

You can save yourself 1 indirect reference by doing it this way

Instead of
=IF(SUM(COUNTIFS($D$2:$D$20,{5,6,7},$H$2:$H$20,99)),F21*2,F21)

Do
=((SUM(COUNTIFS($D$2:$D$20,{5,6,7},$H$2:$H$20,99))>0)+1)*F21

It works perfectly! You are a genius! (although you probably already know that )

You're welcome.