Adding 3 criteria to array

[Joanne]

Hi

I am having problems adding a 3rd criteria to the formula below, any suggestions?

=INDEX('Container Reg.'!$A$2:$A$1686;SMALL(IF(($A$5<='Container Reg.'!$X$2:$X$1686)+($A$7='Container Reg.'!$O$2:$O$1686)=2;ROW('Container Reg.'!$A$2:$A$1686)-ROW('Container Reg.'!$A$2)+1);ROW(A1)))

Thanks
Jo

 

[NARAYANK991]

Hi ,

It will depend on what your 3rd criterion is , and whether this will be ANDed or ORed with the existing criteria.

If you can spell out the full logic using 3 criteria , it will be easy to suggest what your existing formula should be revised to.

Narayan

 

[Joanne]

The 3rd criterion is an AND with respect to the rest.
First criterion in above mentioned formula is a date and refers to column X in the data set.
Second criterion is type of delivery and refers to column O.
Third criterion is container size and refers to column K and should be stated as not including "<>" L C L.
Does this help paint a better picture?
K/R
Jo

 

[NARAYANK991]

Hi ,

Try this :

=INDEX('Container Reg.'!$A$2:$A$1686;SMALL(IF(($A$5<='Container Reg.'!$X$2:$X$1686)+($A$7='Container Reg.'!$O$2:$O$1686) + (ISERROR(SEARCH("L C L" , 'Container Reg.'!$K$2:$K$1686))) = 3;ROW('Container Reg.'!$A$2:$A$1686)-ROW('Container Reg.'!$A$2)+1);ROW(A1)))

This will be an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

 

[Joanne]

Worked like a charm. I just had to translate back to Danish.
Thank you very very much!

 

[Joanne]

I actually have another question:
Would there be another way to write the formula so that the "L C L" criterion becomes a changeable value. By that I mean that in my finished table I am able to change the dates and delivery types and the table changes according to the given parameters. So how could L C L be incorporated in the same manner?

 

[NARAYANK991]

Hi ,

You could enter the L C L criterion in any cell of your choice , and use the cell reference instead of "L C L" in the formula.

Narayan