M
MPI Planner
I need to lookup a product id and return the date of the first value greater
than zero
For instance
a b c d e f g
1 Date 4/12 4/19 4/26 5/3 5/10 5/17
2 Product A 0 11 35 0 0 125
3 Product B 35 50 75 100 25 36
If I lookup Product A in the spreadsheet and want to get results from
columns e through g I would want to see 5/17 as the result.
I have this formula which works against a fixed row reference but have been
unable to incorporate a lookup value into this to return the same result.
INDEX($e$1:$g$1,MATCH(TRUE,INDEX($e:$g2>0,0),0))
How do I add to this formula so that I can type a Product ID into a cell and
lookup and return this data from the table? ie. lookup Product A and show the
first date greater than 0.
than zero
For instance
a b c d e f g
1 Date 4/12 4/19 4/26 5/3 5/10 5/17
2 Product A 0 11 35 0 0 125
3 Product B 35 50 75 100 25 36
If I lookup Product A in the spreadsheet and want to get results from
columns e through g I would want to see 5/17 as the result.
I have this formula which works against a fixed row reference but have been
unable to incorporate a lookup value into this to return the same result.
INDEX($e$1:$g$1,MATCH(TRUE,INDEX($e:$g2>0,0),0))
How do I add to this formula so that I can type a Product ID into a cell and
lookup and return this data from the table? ie. lookup Product A and show the
first date greater than 0.