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?
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?