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

 

[Dim Me as xlNoob]

Hi ExcelRoy,

This should do the trick!

=IF(NOT($Z$2="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))),""),"")

 

[barry houdini]

Do you mean like this?

=IF($Z$2<>"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))),""),"")

 

[Dim Me as xlNoob]

Do you mean like this?

=IF($Z$2<>"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))),""),"")

I honestly don't know what my brain is doing this morning.

 

[ExcelRoy]

Many thanks for your help but not returning anything at all

Maybe because of the second Z2 in the formula?

 

[barry houdini]

OK, so the formula is showing column G values when column L matches Z2. So if Z2 = SCT what do you want the formula to return, just blanks.......and if Z2 contains "xyz" (or something else, what then, do you still want column G values where column L matches Z2?

 

[Dim Me as xlNoob]

Many thanks for your help but not returning anything at all

Maybe because of the second Z2 in the formula?

This seems to be working for me. One question though, where you have "ROWS(AJ$2:AJ2)", is that simply so when you drag the formula down it will then find the next 'nth' match in your list of data since the number of rows in the AJ range will grow by 1 each time the formula moves down a cell?

Are you entering your formula as an array formula with ctrl+shift+enter? This seems to have worked in a mock up spreadsheet for me.

{=IF($Z$2<>"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))),""),"")}

 

[ExcelRoy]

Hi barry & xlNoob,

the formula works fine when "SCT" is found in the list of data (Meaning internal ordering) but I need a list for external ordering hence anything other than "SCT"

I did think that the formula didn't work because of the second mention on Z2 in the formula, wether it needed to be the same a the first part?

I cannot seem to get the formula working, sorry xlNoob

Thanks

 

[admiral100]

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))),""),"")

 

[ExcelRoy]

Hi admiral,

unfortunately not?

its really frustrating as it should "I think" be quite an easy thing to do?

instead of SCT it may be ABC123, DDD01 etc etc but this may change from time to time

Strange thing is if you change the SCT for one of the others on the original formula it works fine with that specific one

Thanks