Min If Array Help

[Kerrymr]

Hi All,

I am working with an excel array to determine the minimum ship date from a series of orders and forecast. There are two tables, one represents orders the other forecast. Both tables have the same headers, Customer, material, and Ship Date. I am trying to use an array formula to return the ship date for each customer material combination. If there is no active order, then the result should search the forecast table for an active forecast.

The data looks something like this....
(reference cell A2 is the word customer in the order table and cell E3 is the word customer in the forecast table.)

Orders Table
Customer Material Ship Date
A 3 9/5/2014
A 3 11/25/2014
A 3 12/29/2014
A 1 9/8/2014
A 1 9/26/2014
A 1 10/7/2014

Forecast Table
Customer Material Ship Date
A 1 9/1/2014
A 1 10/1/2014
A 1 12/1/2014
A 2 10/1/2014
A 2 11/1/2014
A 2 12/1/2014

Results (Array formula below)
A 1 9/1/2014
{=MIN(IF($A12&$B12=$A$3:$A$8&$B$3:$B$8,$C$3:$C$8,IF($A12&$B12=$E$3:$E$8&$F$3:$F$8,$G$3:$G$8,"")))}
A 2 10/1/2014
{=MIN(IF($A13&$B13=$A$3:$A$8&$B$3:$B$8,$C$3:$C$8,IF($A13&$B13=$E$3:$E$8&$F$3:$F$8,$G$3:$G$8,"")))}
A 3 9/5/2014
{=MIN(IF($A14&$B14=$A$3:$A$8&$B$3:$B$8,$C$3:$C$8,IF($A14&$B14=$E$3:$E$8&$F$3:$F$8,$G$3:$G$8,"")))}


When I use this formula, it returns the result of the first result found.

For example, customer A, material 1 should return 9/5/2014, however it appears that since A1 is in row 6 in the orders table and A1 is in row 1 in the forecast table, it is returning the forecast value.

what is the correct way to write this array?

 

[Andrew Poulsom]

Welcome to MrExcel.

Formula in C12 copied down:

Excel 2010
	A	B	C	D	E	F	G
1	Orders Table				Forecast Table
2	Customer	Material	Ship Date		Customer	Material	Ship Date
3	A	3	05/09/2014		A	1	01/09/2014
4	A	3	25/11/2014		A	1	01/10/2014
5	A	3	29/12/2014		A	1	01/12/2014
6	A	1	08/09/2014		A	2	01/10/2014
7	A	1	26/09/2014		A	2	01/11/2014
8	A	1	07/10/2014		A	2	01/12/2014
9
10
11
12	A	1	01/09/2014
13	A	2	01/10/2014
14	A	3	05/09/2014
Sheet1

Cell Formulas
Range	Formula
C12	{=MIN(IF(A$3:A$8=A12,IF(B$3:B$8=B12,C$3:C$8)),IF(E$3:E$8=A12,IF(F$3:F$8=B12,G$3:G$8)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Kerrymr]

Thanks for the quick response!
I note however, that the result of your array returns value 1/9/2014 for customer A, material 1.
I am trying to have the array return the result from the orders table when there is an order.
In the example you provided it returns the result from the forecast table when an order exists.

Any thoughts?

 

[Andrew Poulsom]

Does this work for you?

=MIN(IF(COUNTIFS(A$3:A$8,A12,B$3:B$8,B12),IF(A$3:A$8=A12,IF(B$3:B$8=B12,C$3:C$8)),IF(E$3:E$8=A12,IF(F$3:F$8=B12,G$3:G$8))))

Don't forget Ctrl+Shift+Enter.

 

[Peter Thompson]

I would use a helper column in column A and then break the element of the array into separate cells

Helper Column (inn a3 down to the calculation rows)
=D3&C3

E12 {=IF(COUNTIF(A$3:A$8,A12)>0,MIN(IF(A$3:A$8=A12,E$3:E$8,999999999)),999999999) }

F12 {=MIN(IF(F$3:F$8=A12,I$3:I$8,999999999))}

G12 =IF(AND(E13>0,E13<>999999999),E13,F13)

Note the 999999999 is just an arbitary large number to ensure that it doesn't get picked.

 

[Kerrymr]

This solution worked.
=MIN(IF(COUNTIFS(A$3:A$8,A12,B$3:B$8,B12),IF(A$3:A$8=A12,IF(B$3:B$8=B12,C$3:C$8)),IF(E$3:E$8=A12,IF(F$3:F$8=B12,G$3:G$8) )))

Why did the first one fail? Like mine, I would have expected it to work.