Help with Formulas

[Laura_Hicks]

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!

Laura

[#2]

@Laura_Hicks 

That could be

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

[#3]

@Laura_Hicks 

In H116:

=IF(G116>0,INDEX(Sheet1!$E$2:$E$49,MATCH(1,INDEX(ISNUMBER(SEARCH(C116&"",Sheet1!$E$2:$E$49))*ISNUMBER(SEARCH(IF($B$11="Fixed","FX","TM"),Sheet1!$E$2:$E$49)),),0)),"")

In I116:

=IF(G116>0,INDEX(Sheet1!$A$2:$A$49,MATCH(1,INDEX(ISNUMBER(SEARCH(C116&"",Sheet1!$E$2:$E$49))*ISNUMBER(SEARCH(IF($B$11="Fixed","FX","TM"),Sheet1!$E$2:$E$49)),),0)),"")

Fill down.