Array formula based on 2 criterias

[forest1959]

Hi All

I know this will be pretty straight forward, but i'm struggling to ask the web the right question..

I have an array formula that brings back an "order number" in column E, based on the order date in column A

{=IF(ROWS(A$35:A35)>COUNTIF(Timesheets!$A2:$A2000,$E$31),"",INDEX(Timesheets!$E2:$E2000,SMALL(IF(Timesheets!$A2:$A2000=$E$31,ROW(Timesheets!$A2:$A2000)-ROW(Timesheets!$A$2)+1),ROWS(A$35:A35))))}

I just need to add another criteria based on the employee's name populated in column C

Below is an example of the results i'm looking for

[TABLE="width: 427"]
<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Criteria 1[/TD]
[TD]Criteria 2[/TD]
[TD]Results[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]05/04/2019[/TD]
[TD]Brian[/TD]
[TD]3456[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5678[/TD]
[/TR]
[TR]
[TD]31/03/2019[/TD]
[TD]Brian[/TD]
[TD]1234[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]31/03/2019[/TD]
[TD]Chris[/TD]
[TD]2345[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05/04/2019[/TD]
[TD]Brian[/TD]
[TD]3456[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05/04/2019[/TD]
[TD]Chris[/TD]
[TD]4567[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05/04/2019[/TD]
[TD]Brian[/TD]
[TD]5678[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05/04/2019[/TD]
[TD]Chris[/TD]
[TD]6789[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

regards
Paul

 

[James006]

Hi,

Could you test following .

=IF(ROWS(A$35:A35)>COUNTIF(Timesheets!$A2:$A2000,$E$31),"",INDEX(Timesheets!$E2:$E2000,SMALL(IF(Timesheets!$A2:$A2000=$E$31,IF(Timesheets!$C2:$C$2000=$F$31,ROW(Timesheets!$A2:$A2000)-ROW(Timesheets!$A$2)+1),ROWS(A$35:A35)))))

HTH

 

[forest1959]

Thank you, it comes with an error saying i've entered too few arguments for this function

 

[James006]

Where exactly is Brian ... your new value for your new criteria ... ???

My guess was cell F31 ... is that right ?

 

[forest1959]

correct

 

[forest1959]

When i ok the error message the following is visible on the screen

SMALL(array, k)

 

[James006]

OK ...

must be a misplaced parenthesis ... somewhere ...

Have a try with

=IF(ROWS(A$35:A35)>COUNTIF(Timesheets!$A2:$A2000,$E$31),"",INDEX(Timesheets!$E2:$E2000,SMALL(IF(Timesheets!$A2:$A2000=$E$31,IF(Timesheets!$C2:$C$2000=$F$31,ROW(Timesheets!$A2:$A2000)-ROW(Timesheets!$A$2)+1)),ROWS(A$35:A35))))

HTH

 

[forest1959]

OK ...

must be a misplaced parenthesis ... somewhere ...

Have a try with

=IF(ROWS(A$35:A35)>COUNTIF(Timesheets!$A2:$A2000,$E$31),"",INDEX(Timesheets!$E2:$E2000,SMALL(IF(Timesheets!$A2:$A2000=$E$31,IF(Timesheets!$C2:$C$2000=$F$31,ROW(Timesheets!$A2:$A2000)-ROW(Timesheets!$A$2)+1)),ROWS(A$35:A35))))

HTH

Almost, it brings back the first order number as it should, but then brings back every second rowed order number below it

so for example order numbers
1 Chris
2 Brian
3 Brian
4 Chris
5 Brian

and so on

where 2 is Brians first order number, its bringing back 2, but then 4, 6, 8 etc

 

[James006]

Well testing an array formula ... without the underlying data ... is always tricky ...

What about :

=IF(ROWS(A$35:A35)>COUNTIF(Timesheets!$A2:$A2000,$E$31),"",INDEX(Timesheets!$E2:$E2000,SMALL(IF(Timesheets!$C2:$C$2000=$F$31,IF(Timesheets!$A2:$A2000=$E$31,ROW(Timesheets!$A2:$A2000)-ROW(Timesheets!$A$2)+1)),ROWS(A$35:A35))))

 

[forest1959]

Well testing an array formula ... without the underlying data ... is always tricky ...

What about :

=IF(ROWS(A$35:A35)>COUNTIF(Timesheets!$A2:$A2000,$E$31),"",INDEX(Timesheets!$E2:$E2000,SMALL(IF(Timesheets!$C2:$C$2000=$F$31,IF(Timesheets!$A2:$A2000=$E$31,ROW(Timesheets!$A2:$A2000)-ROW(Timesheets!$A$2)+1)),ROWS(A$35:A35))))

I really appreciate your persistence. in the same example as above, this time it's bringing the 2 back (as it should), but nothing thereafter