data return using multiple conditions

[Steven M.]

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!

 

[ProfessionalExcel.com]

Steven,

Two approaches that solve your problem are:

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.]

Thanks Chris,

I used step 2 with the Array and it works great, except on some items I left
out of the original post.

For Column D - Line, some of the contents end in a letter... ex. 1001A.
The formula works great if the entire content is numeric ex. 10011. Is there
a way to also return the cell contents that contain a letter in the value?

 

[ProfessionalExcel.com]

Steven,

Yes, the limitation of the first two approaches was that the value returned
had to be numeric. The third approach below will return both text and numeric
values:

3) Using an Array formula (which requires using Ctrl+Shift+Enter when
entering the formula):
=INDEX(Sheet2!$D$2:$D$4,MATCH(1,(A2=Sheet2!$A$2:$A$4)*(B2>=Sheet2!$B$2:$B$4)*(B2<=Sheet2!$C$2:$C$4),0))


Hope that helps.


----------------------------
Please rate this post if it answers your question.

Thanks,

Chris
http://www.ProfessionalExcel.com

 

[Steven M.]

Works like a charm!

Thanks so much for your help!