Trouble with multiple criteria array in XL10

[larsonnj]

I am trying to draw some analysis from a long list of different sales made to see how often each client returns to make a new purchase. I have made an array which works for a single criteria, but now I am looking to add a second criteria.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]1st Sale[/TD]
[TD]2nd Sale[/TD]
[TD]3rd Sale[/TD]
[TD]4th Sale[/TD]
[/TR]
[TR]
[TD]Company X[/TD]
[TD]02/07/2010[/TD]
[TD]07/13/2012[/TD]
[TD]08/10/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company Y[/TD]
[TD]04/23/2009[/TD]
[TD]04/15/2011[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My out put table looks similar to the above (with a lot more entries). The formula I used for the single criteria is the following (this works):

=IFERROR(INDEX(Sales!$E$6:$E$2501, SMALL(IF($A152=Sales!$C$6:$C$2501, ROW(Sales!$C$6:$C$2501)-MIN(ROW(Sales!$E$6:$E$2501))+1, ""), COLUMN(A1))),"")

In column E of the Sales tab is listed the date of the sale, and C lists the company name. Column A of the sheet I am working on has a unique list of all previous customers. This formula works fine. Now I want to also be able to screen for a specific type of sale which is listed on the Sales sheet in column D.

I tried using the following formula but it gives me a #VALUE! error.

=IFERROR(INDEX(Sales!$E$6:$E$2501, SMALL(IF(And($A151=Sales!$C$6:$C$2501, $B$139=Sales!$D$6:$C$2501), ROW(Sales!$C$6:$C$2501)-MIN(ROW(Sales!$E$6:$E$2501))+1, ""), COLUMN(A1))),"")

The bold part is the part I have changed in an attempt to not only validate the company name but also the type (which I have input into B139).

I hope this makes sense. Any advice for creating the formula to check for more than one criteria before populating the array?

 

[RonB1111]

Check this part of the formula: Sales!$D$6:$C$2501 --- should be Sales!$D$6:$D$2501

 

[larsonnj]

Sorry that was a typo. Still giving me the #VALUE! error

 

[larsonnj]

I figured this one out. Just needed to add a couple more if statements into the formula and it works just fine.