Steven,
Two approaches that solve your problem a
1) Using the SUMPRODUCT function:
Copy this formula into C2 on Sheet1, then copy down for remaining rows:
=SUMPRODUCT((A2=Sheet2!$A$2:$A$4)*(B2=Sheet2!$B$2 :$B$4)*(B2<=Sheet2!$C$2:$C$4)*Sheet2!$D$2:$D$4)
2) Using an Array formula (which requires using Ctrl+Shift+Enter when
entering the formula):
Copy this formula into C2 on Sheet1, then copy down for remaining rows:
=SUM(IF((A2=Sheet2!$A$2:$A$4)*(B2=Sheet2!$B$2:$B$ 4)*(B2<=Sheet2!$C$2:$C$4),Sheet2!$D$2:$D$4,0))
Obviously if you posted only a sample of your Sheet2 data, you will need to
adjust the dimensions of the Sheet2 ranges specified in the formulas above.
This approach does require that only a single row in Sheet2 will meet the
conditions you are looking up, which sounds like it does.
----------------------------
Please rate this post if it answers your question.
Thanks,
Chris
http://www.ProfessionalExcel.com
"Steven M." wrote:
I am trying to return the contents of a cell on a row which must meet
multiple criteria. I have 2 worksheets, one which contains the criteria, the
other contains the matching criteria and the cell that needs to be returned.
The problem I have is getting the criteria to match and return the data in
the correct cell in that row.
Here is an example of the data on Sheet 1:
A B
C
Line Segment Value
(Line) return from sheet 2
4 digit number number with 2 decimal places 5 digit number
1001 30.86
10012
On sheet 2 I have the data that must be matched and the data to return:
A B
C D
Line Segment Beginning Value Ending Value
Line
1001 0.00 28.63
10011
1001 28.64 40.32
10012
1001 40.33 70.59
10013
The Line segment on sheet 1 (A) must equal the line segment on sheet 2 (A).
The value on sheet 1 (B) must be greater than or equal to the beginning
value on sheet 2 (B).
The value on sheet 1 (B) must also be less than or equal to the ending value
on sheet 2 (C).
When these criteria are met I want the Line from sheet 2 (D) returned to
sheet 1 (C).
I'm not sure how to accomplish this. Help anyone?
Thanks!