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!

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!

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" wrote:

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!

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." wrote:

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" wrote:

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!

Works like a charm!

Thanks so much for your help!

"ProfessionalExcel.com" wrote:

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." wrote:

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" wrote:

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!