Need help with a lookup/match

[Hat_Trick]

Hello,
I am working on a manufacturing tool and I need help determining a date.

My set up here takes the on dock date and calculates back to a ship date, as well as a proposed start date.

The model takes into account the number of hours per unit (variable depending on product), against the production line capacity to determine the number of units per day, which translates to the number of days to complete the production run.

It has the ability to adjust the line for OT, or adding/subtracting people.

The problem is, when you start playing with the capacity, the proposed start date is no longer really valid, so I need to determine the *ACTUAL* start date.

I have tried =INDEX($S$3:QC9,1,MATCH(LARGE($S7:$QC7,1),$S7:$QC7,0)) but the problem is that if I bring in some over time or add people mid-production run, the actual start date would shift to that day.

so basically, I need something that will look down the columns, and when it hits the first cell with a value >0 it will look up the date for the column and return it as a solution

Does anyone have any suggestions?

thanks!

[Roger Govier]

Hi

Try the array entered formula

{=INDEX($S$3:QC9,1,MATCH(1,ISNUMBER(S7:QC7)*(S7:QC7>0),0))}

To enter or amend an Array formula, use Control+Shift+Enter (CSE) not just Enter.
Do not type the curly braces { } yourself. If you use CSE, Excel will insert them.

Or, if you want to make the formula shorter and easier to read, you could create a named formula.
Place your cursor in cell G2
Define Name > Fdate >
Refers to =MATCH(1,ISNUMBER('Stow Box'!S8:QC8)*('Stow Box'!S8:QC8>0),0)

then your formula in G8 would be the array formula
{=INDEX($S$3:QC9,1,Fdate)}