# Help with Formulas

Occasional Visitor

# Help with Formulas

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 Replies

# Re: Help with Formulas

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)), "---")``````

# Re: Help with Formulas

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.