Help with Formulas

Occasional Visitor

I need some help with formulas/functions for the attached screen shot/file.  I need a couple things to happen.

1.  If there is a dollar value in G116 through G123 greater than $0, then I need a value populated I in column H (the value would be an Item #).

2. The Item #  should then be determined off of 2 fields, and another sheet within the Excel File. 

     a.  Should be based off of cell B11, which could be Fixed or T&M and then also the Product Number in column C.  Then look at Sheet 1, column E to determine what the Item # should be.

     b.  Example, cell G120 has a value larger than $0, so I need an Item # to populate in H120.  Cell B11 = Fixed, and cell C120 has Product Number 310, so looking at Sheet 1 I need it to populate: PS-FX-310.  

3. Then, based off the above, I need the Material # to populate based off of column H.

     a.  For the example above, PS-FX-310 would be the Item # for column H, and then the corresponding Material Number from Sheet 1 would populate 110010791 in cell I120.


Hope that makes sense!  Thanks in advance!



2 Replies


That could be

=IF( G116 > 0,  INDEX(Sheet1!$E$3:$E$50,
   MATCH(1, INDEX( ISNUMBER(SEARCH($B$11,Sheet1!$B$3:$B$50))*
0)), "---")


In H116:


In I116:


Fill down.