I have been working on writing an if statement to lookup or index match values on another spreadsheet, but I have not been able to write something that will satisfy all of the criteria, therefore, looking for some help.
I am trying to looking a production quantity on a separate tab, where value in column L = 0. I would also like the formula to:
*If the first instance of 0in column L for the same item in column B, pull back the first production quantity listed as these will be sorted in chronological order.
*If not the first 0 for the same item, check whether the quantity pulled back for the line above will satisfy the open order quantity on the line above and the corresponding value in column I. If, not then pull back the next production quantity.
*If there is no corresponding production value on the lookup, then I would like to reflect a "0" or "no production".
*If the value in column L is greater than Zero or there is a break between items, I would like to return a blank
Here is what I have written so far, I seem to be running into issues getting all of these things to work together.
=IFERROR(IF(OR(VALUE(L4) > 0,A4=" "),"",IF(AND(VALUE(L4)=0,B3="Description"),VLOOKUP(B4,Sched!A:C,3,0),IF(B4=" ","",IF(AND(VALUE(L4)=0,VALUE(L3) <> 0),INDEX(Sched!C:C,MATCH(Detail!B4,Sched!A:A,0)),IF(AND(VALUE(L4)=0,VALUE(L3)=0,VLOOKUP(B4,Sched!A:C,3,0)-I3 < I4),INDEX(Sched!C:C,MATCH(Detail!B4,Sched!A:A,0)+1),INDEX(Sched!C:C,MATCH(Detail!B4,Sched!A:A,0))))))),"-")
I am trying to looking a production quantity on a separate tab, where value in column L = 0. I would also like the formula to:
*If the first instance of 0in column L for the same item in column B, pull back the first production quantity listed as these will be sorted in chronological order.
*If not the first 0 for the same item, check whether the quantity pulled back for the line above will satisfy the open order quantity on the line above and the corresponding value in column I. If, not then pull back the next production quantity.
*If there is no corresponding production value on the lookup, then I would like to reflect a "0" or "no production".
*If the value in column L is greater than Zero or there is a break between items, I would like to return a blank
Here is what I have written so far, I seem to be running into issues getting all of these things to work together.
=IFERROR(IF(OR(VALUE(L4) > 0,A4=" "),"",IF(AND(VALUE(L4)=0,B3="Description"),VLOOKUP(B4,Sched!A:C,3,0),IF(B4=" ","",IF(AND(VALUE(L4)=0,VALUE(L3) <> 0),INDEX(Sched!C:C,MATCH(Detail!B4,Sched!A:A,0)),IF(AND(VALUE(L4)=0,VALUE(L3)=0,VLOOKUP(B4,Sched!A:C,3,0)-I3 < I4),INDEX(Sched!C:C,MATCH(Detail!B4,Sched!A:A,0)+1),INDEX(Sched!C:C,MATCH(Detail!B4,Sched!A:A,0))))))),"-")
