OR function in an array

[toongal12]

So I am trying to add an OR function to my array but I either get all False or all True.

Compared to a certain date, I want to display the date of when an item is upgraded with either:
Type_1
Type_2 with Ver_1
Type_2 with Ver_2

If any other item type comes up (example: an item has an upgrade of a Type_4), it should be ignored and not displayed. Right now my code is displaying each item and their upgrade date and type.

Example Data of an 4 item list:
Column A: List of Items (A1 is the reference for the first item. It will change based on the item)
Column B: Dates to be upgraded (B6 is the date being compared)
Column C: End of upgrade
Column E: List of Types
Column F: List of versions


=IFERROR(INDEX(B1:B4,MATCH(A1&MIN(IF((A1:A4=A1)*(B1:B4>=B6)*((E1:E4="Type_1" >0)+(AND(E1:E4="Type_2",F1:F4="Ver_1")>0)+(AND(E1:E4="Type_2",F1:F4="Ver_2")>0)),IF(B1:B4>=A6,C1:C4))),A1:A4&C1:C4,0)),"-")

 

[Special-K99]

You cant add an AND in an array formula.
For example, the commonly seen formula when removing blanks:

=IFERROR(INDEX($A$1:$A$100,SMALL(IF(($A$1:$A$100<>"")*(B$1:B$100=1),ROW($A$1:$A$100)),ROW(A1)),1),"")
The above works

But this doesn't
=IFERROR(INDEX($A$1:$A$100,SMALL(IF(AND($A$1:$A$100<>"",B$1:B$100=1),ROW($A$1:$A$100)),ROW(A1)),1),"")

Expand your AND's to IF(...,IF(...)))
or
IF((...)*(...)) as youve done
Use + for OR

 

[toongal12]

You cant add an AND in an array formula.
For example, the commonly seen formula when removing blanks:

=IFERROR(INDEX($A$1:$A$100,SMALL(IF(($A$1:$A$100<>"")*(B$1:B$100=1),ROW($A$1:$A$100)),ROW(A1)),1),"")
The above works

But this doesn't
=IFERROR(INDEX($A$1:$A$100,SMALL(IF(AND($A$1:$A$100<>"",B$1:B$100=1),ROW($A$1:$A$100)),ROW(A1)),1),"")

Expand your AND's to IF(...,IF(...)))
or
IF((...)*(...)) as youve done
Use + for OR

((E1:E4="Type_1" >0)+(AND(E1:E4="Type_2",F1:F4="Ver_1")>0)+(AND(E1:E4="Type_2",F1:F4="Ver_2")>0))

So for the above part, you're saying it should be changed to:

((E1:E4="Type_1" >0)+IF((E1:E4="Type_2")*(F1:F4="Ver_1")>0)+(IF(E1:E4="Type_2)*(*F1:F4="Ver_2")>0))

 

[toongal12]

I get an error. When I remove the ">0" with ",0" it returns all false

 

[Aladin Akyurek]

Care to post an 8 row sample along with the expected output?

 

[toongal12]

Care to post an 8 row sample along with the expected output?

Special-K99 was close to helping me, I'm just having trouble implementing it.

I know the formula works everywhere except:

=IFERROR(INDEX(B1:B4,MATCH(A1&MIN(IF((A1:A4=A1)*(B1:B4>=B6)*((E1:E4="Type_1" >0)+(AND(E1:E4="Type_2",F1:F4="Ver_1")>0)+(AND(E1:E4="Type_2",F1:F4="Ver_2")>0)),IF(B1:B4>=A6,C1:C4))),A1:A4&C1:C4,0)),"-")

The bolded part is what is recently added. It's the part I'm trying to fix. Changing it to:


((E1:E4="Type_1" >0)+IF((E1:E4="Type_2")*(F1:F4="Ver_1")>0)+(IF(E1:E4="Type_2)*(*F1:F4="Ver_2")>0))

gave me errors though. I'm having trouble with the AND and OR part

 

[Special-K99]

This

((E1:E4="Type_1" >0)+(AND(E1:E4="Type_2",F1:F4="Ver_1")>0)+(AND(E1:E4="Type_2",F1:F4="Ver_2")>0))

(E1:E4="Type_1" >0) this doesnt make sense, what's greater than 0? The COUNT of E1:E4 being= "TYpe_1"?

Use COUNTIF(E1:E4,"Type_1")

Same goes for the > 0 in the formula

Perhaps you want this?

=(COUNTIF(E1:E4,"Type_1")>0)+(COUNTIFS(E1:E4,"Type_2",F1:F4,"Ver_1")>0)+(COUNTIFS(E1:E4,"Type_2",F1:F4,"Ver_2")>0)