Slight change to formula

[ExcelRoy]

Hi,

Can someone please assist me in changing the following formula to work if the criteria in Z2 is anything other than "SCT"

=IF($Z$2<>"",IFERROR(INDEX(Backorders!$G$2:$G$1000,SMALL(IF($L$2:$L$1000=$Z$2,ROW($L$2:$L$1000)-ROW($L$2)+1),ROWS(AJ$2:AJ2))),""),"")

Many thanks

 

[admiral100]

Can you please provide an example with a little data

 

[ExcelRoy]

hi admiral,

Column B : Alpha Numeric Job Reference
Col C, D, E, F with Customer details
Col G : Quantity
Col H : Part Number
Col I : Description

Col L : Supplier Code "SCT" or anything else

I would like data for everything as SCT and everything else other than SCT

The SCT data works fine but the non SCT shows nothing

Sorry for the poor explanation

Thanks

 

[ExcelRoy]

Whilst playing around with the formulal I have noticed that whatever you change in the quotations doesn't matter?

Should this be the case?

 

[Aladin Akyurek]

Just a guess

=IF($Z$2<>"SCT",IFERROR(INDEX(Backorders!$G$2:$G$1000,SMALL(IF(AND($L$2:$L$1000=$Z$2,$Z$2<>"SCT"),ROW($L$2:$L$1000)-ROW($L$2)+1),ROWS(AJ$2:AJ2))),""),"")

AND cannot return an array, just a scalar, while the formula needs an array of multiple evaluations...

 

[Aladin Akyurek]

1. Z2 = SCT

{=IFERROR(INDEX(Backorders!$G$2:$G$1000,SMALL(IF($L$2:$L$1000=$Z$2,ROW($L$2:$L$1000)-ROW($L$2)+1),ROWS(AJ$2:AJ2))),"")}

2. Anything else but Z2 =SCT

{=IFERROR(INDEX(Backorders!$G$2:$G$1000,SMALL(IF(1-($L$2:$L$1000=$Z$2),ROW($L$2:$L$1000)-ROW($L$2)+1),ROWS(AJ$2:AJ2))),"")}

 

[admiral100]

Hi,

I played a bit with the formula and the data, in my opinion the problem is that $L$2:$L$1000=$Z$2 return FALSE so you got nothing
Although $Z$2 is different from "SCT"

Let's say Z2 = "abc" BUT $L$2:$L$1000 <> "abc" so you got nothing...

 

[ExcelRoy]

Hi,

Can we change Z2 to a range say Z2:Z1000 ?

 

[Aladin Akyurek]

Hi,

Can we use a named range consisting of all possible alternatives within the formula to replace the Z2?

I have had a little go but cannot seems to get it working?

Thanks

Did you try post #15 ?

 

[ExcelRoy]

Hi Aladin,

I think I have been tying myself up in knots somewhat. Apologies for this

I required a list of SCT orders and a list of Non-SCT Orders, I worked out the SCT list and that works great Z2="SCT" but then ...

I was trying to use the same formula for anything other than SCT whilst remaining Z2 as SCT, of course this was returning nothing, somewhat embarrassed I feel at the moment

I am not sure there is a way to use the same formula for non SCT orders (Listed in Column L) instaed of Z2

Sorry for wasting everyones time here but hopefully a solution is possible still?

Sorry once again

 

[Aladin Akyurek]

Hi Aladin,

I think I have been tying myself up in knots somewhat. Apologies for this

I required a list of SCT orders and a list of Non-SCT Orders, I worked out the SCT list and that works great Z2="SCT" but then ...

I was trying to use the same formula for anything other than SCT whilst remaining Z2 as SCT, of course this was returning nothing, somewhat embarrassed I feel at the moment

I am not sure there is a way to use the same formula for non SCT orders (Listed in Column L) instaed of Z2

Sorry for wasting everyones time here but hopefully a solution is possible still?

Sorry once again

Z2 must house SCT

1. SCT sublist...

{=IFERROR(INDEX(Backorders!$G$2:$G$1000,SMALL(IF($L$2:$L$1000=$Z$2,ROW($L$2:$L$1000)-ROW($L$2)+1),ROWS(AJ$2:AJ2))),"")}

2. Non-SCT sublist...

{=IFERROR(INDEX(Backorders!$G$2:$G$1000,SMALL(IF(1-($L$2:$L$1000=$Z$2),ROW($L$2:$L$1000)-ROW($L$2)+1),ROWS(AJ$2:AJ2))),"")}

​