nesting if statements with "or" elements in an array - not working

[splreece]

morning all,

this is an evolved formula based on an old thread so I wasn't sure whether it should be a new thread or same (different problem).

I have an array formula which works a treat and bring back any acct referenced to "BACS". (see below).

=IFERROR(INDEX(DataPull!$A$1:$A$2000,SMALL(IF(DataPull!L:L="BACS",IF(DataPull!$K$1:$K$2000>=$P$3+0,IF(DataPull!$K$1:$K$2000<=$P$4+0,ROW(DataPull!$C$1:$C$2000)-ROW(DataPull!$C$1)+1))),ROWS($A$1:$A1))),"")


However I want to bring back any references to meet any of the criteria, "BACS" "TP" "CASH" "PO".

So the nested formula is:

=IFERROR(INDEX(DataPull!$A$1:$A$2000,SMALL(IF(OR(DataPull!L:L="BACS",DataPull!L:L="TP",DataPull!L:L="CASH",DataPull!L:L="PO"),IF(DataPull!$K$1:$K$2000>=$P$3+0,IF(DataPull!$K$1:$K$2000<=$P$4+0,ROW(DataPull!$C$1:$C$2000)-ROW(DataPull!$C$1)+1))),ROWS($A$1:$A1))),"")



This unfortunately is bringing back ANY reference so the array is finding the first occasion then just copying anything in the dataset whether it meets if/or or not.

Any ideas why this may be happening?

 

[Aladin Akyurek]

In A1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(DataPull!$A$1:$A$2000,
    SMALL(IF(ISNUMBER(MATCH(DataPull!$L$:$L$2000,{"BACS","TP","CASH","PO"},0)),
    IF(DataPull!$K$1:$K$2000>=$P$3+0,IF(DataPull!$K$1:$K$2000<=$P$4+0,
    ROW(DataPull!$C$1:$C$2000)-ROW(DataPull!$C$1)+1))),ROWS($A$1:$A1))),"")

 

[splreece]

In A1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(DataPull!$A$1:$A$2000,
    SMALL(IF(ISNUMBER(MATCH(DataPull!$L$:$L$2000,{"BACS","TP","CASH","PO"},0)),
    IF(DataPull!$K$1:$K$2000>=$P$3+0,IF(DataPull!$K$1:$K$2000<=$P$4+0,
    ROW(DataPull!$C$1:$C$2000)-ROW(DataPull!$C$1)+1))),ROWS($A$1:$A1))),"")

Thanks for the quick reply.

The formula is in and ctrl shift and enter to array it.

In the evaluate formula the initial (MATCH(DataPull!$L$:$L$2000,{"BACS","TP","CASH","PO"},0) element looks to be only bringin back n/a for all cycled entries so it isn't picking up a match for any bacs/tp/cash/po.

 

[steve the fish]

Change DataPull!$L$:$L$2000 to DataPull!$L$1:$L$2000

 

[splreece]

Thanks for the quick reply.

The formula is in and ctrl shift and enter to array it.

In the evaluate formula the initial (MATCH(DataPull!$L$:$L$2000,{"BACS","TP","CASH","PO"},0) element looks to be only bringin back n/a for all cycled entries so it isn't picking up a match for any bacs/tp/cash/po.

I have tweaked it but the array now brings back 2 random entries (1 of the entries is a "PDQ" entry therefore shouldn't be in the list)


{=IFERROR(INDEX(DataPull!$A$1:$A$2000,
SMALL(IF(ISNUMBER(MATCH(DataPull!$L$1:$L$2000,{"BACS","TP","CASH","PO"},0)),
IF(DataPull!$K$1:$K$2000>=$AB$3+0,IF(DataPull!$K$1:$K$2000<=$AB$4+0,
ROW(DataPull!$C$1:$C$2000)-ROW(DataPull!$C$1)+1))),ROWS($A$1:$A1))),"")}

If I rejig the formula =IFERROR(INDEX(DataPull!$A$1:$A$2000,
SMALL(IF(ISNUMBER(MATCH({"BACS","TP","CASH","PO"},DataPull!$L$1:$L$2000,0)),
IF(DataPull!$K$1:$K$2000>=$AB$3+0,IF(DataPull!$K$1:$K$2000<=$AB$4+0,
ROW(DataPull!$C$1:$C$2000)-ROW(DataPull!$C$1)+1))),ROWS($A$1:$A1))),"")

it then brings every entry back after the first found entry.

 

[Aladin Akyurek]

I have tweaked it but the array now brings back 2 random entries (1 of the entries is a "PDQ" entry therefore shouldn't be in the list)


{=IFERROR(INDEX(DataPull!$A$1:$A$2000,
SMALL(IF(ISNUMBER(MATCH(DataPull!$L$1:$L$2000,{"BACS","TP","CASH","PO"},0)),
IF(DataPull!$K$1:$K$2000>=$AB$3+0,IF(DataPull!$K$1:$K$2000<=$AB$4+0,
ROW(DataPull!$C$1:$C$2000)-ROW(DataPull!$C$1)+1))),ROWS($A$1:$A1))),"")}

If I rejig the formula =IFERROR(INDEX(DataPull!$A$1:$A$2000,
SMALL(IF(ISNUMBER(MATCH({"BACS","TP","CASH","PO"},DataPull!$L$1:$L$2000,0)),
IF(DataPull!$K$1:$K$2000>=$AB$3+0,IF(DataPull!$K$1:$K$2000<=$AB$4+0,
ROW(DataPull!$C$1:$C$2000)-ROW(DataPull!$C$1)+1))),ROWS($A$1:$A1))),"")

it then brings every entry back after the first found entry.

It should be:

=IFERROR(INDEX(DataPull!$A$1:$A$2000, SMALL(IF(ISNUMBER(MATCH(DataPull!$L$1:$L$2000,{"BACS","TP","CASH","PO"},0)), IF(DataPull!$K$1:$K$2000>=$P$3+0,IF(DataPull!$K$1:$K$2000<=$P$4+0, ROW(DataPull!$C$1:$C$2000)-ROW(DataPull!$C$1)+1))),ROWS($A$1:$A1))),"")


This formula simply extends the term of the original which involves the L-range: The match term means if L-range is BACS or TP or CASH or PO. Care to specify exactly how it fails?

 

[splreece]

Thanks for all the help.

I am not sure what I did or where I went wrong, but by wiping the sheet, starting afresh and retyping the above formula it is working perfectly.

I have been able to add more ifs which bring back correct results so I know it can be amended if other payment types are required.

Thanks again to all responses, this resource is invaluable, its a huge learning curve of which I am really crossing that excel/access logic difference (and its a big difference).

 

[Aladin Akyurek]

You are welcome. Glad to hear that it's sorted out.