Help with Sumproduct!!!

[trone77]

I need a formula that will return the value of a cell from another workbook if it meets a certain criteria. For example, if $F$10 is equal to 15, then it would return the contents of $A$10 on that same row of that worksheet. How could the following formula be revised to do just that.

=SUMPRODUCT(--('[Monthly Tally 2010.XLT]Aug'!$F$8:$F$38=15)

 

[Special-K99]

Don't use SUMPRODUCT

Use this instead

=INDEX(A8:F38,MATCH(15,F8:F38,0),1)

 

[trone77]

So how would I incorporate the references, would it look like the following?


=index('[Monthly Tally 2010.XLT]Aug'!$A$8:$F$38,Match(15,$F$8:$F$38,0)1)

 

[schielrn]

With Index you don't need to necessarily pass the whole range in the first part:

=index('[Monthly Tally 2010.XLT]Aug'!$A$8:$A$38,Match(15,'[Monthly Tally 2010.XLT]Aug'!$F$8:$F$38,0))

Hope that helps.

You were missing a comma before the 1 at the end of your index formula and don't you need to specify the sheet in your match formula?

 

[trone77]

Tried that formula but still received a #N/A error message. The sheets that I am drawing info from get updated on a daily basis, so some of the cells remain empty until entry, would that affect the formula?

 

[trone77]

Disregard the previous post, I got it to work....thanks!!!