how to return a value with two conditions.

[skyduck]

A B C D
-------- ----- ------ ---
G04-201A 0 0.76 898
G04-201A 0.76 1.07 860
G04-201A 1.07 3.66 830
G04-201A 3.05 0
G04-201A 3.66 4.57
G04-201A 4.57 0
G04-201C 0 0.91 898
G04-201C 0.76 0
G04-201C 0.91 1.68 830
G04-201C 1.68 2.74
G04-201C 2.74 4.27 830
G04-201C 4.27 5.79
G04-201C 5.79 0

E F G
-------- ----- ------
G04-201A 1.52
G04-201A 2.29
G04-201A 3.05
G04-201C 0.61
G04-201C 1.52
G04-201C 3.5

here is what i want to do with each row in column 'g'

- match column 'e' with column 'a'
- match column 'f' such that it is in between column 'b'
and 'c'
- if the above conditions are true, return the value from
column 'd' to column 'g'

example, 5th row in column 'e' and 'f'
'e' = G04-201C, 'f' = 1.52
therefore, returns 830 from column 'd'

thx in advance.

 

[JE McGimpsey]

one way:

=SUMPRODUCT(--(A1:A100=E1),--(B1:B100<=F1),--(C1:C100>=F1),D1100)

 

[Frank Kabel]

Hi
if your data in column A, b, C is sorted ascending try the following
formula in G1
=INDEX($D$1:$D$100,MATCH(F1,OFFSET($B$1,MATCH(E1,$A$1:$A$100,0)-1,0,20)
,1))

 

[Jason Morin]

=INDEX($D$1:$D$13,MATCH(1,(F1>=$B$1:$B$13)*(F1<=$C$1:$C$13)
*(E1=$A$1:$A$13),0))

Array-entered (press ctrl/shift/enter).

HTH
Jason
Atlanta, GA

 

[Guest]

Hi skyduck
Try the following formula in cell G1

=SUMPRODUCT(($A$1:$A$20=E1)*($B$1:$B$20<F1)*($C$1:$C$20>F1)*($D$1:$D$20)

The ranges all need to be the same length and they cannot be column references (ex. A:A)

The first three components in this function will produce a 1 if it is true and a zero if it isn't. If a value falls into more then one catagory you will get the sum of the values in column D

In your example a value of G04-201A and 3.50 fall into more then one catagory. Which number do you want to return

Let me know if this formula needs to be tweeked

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- skyduck wrote: ----

A B C
-------- ----- ------ --
G04-201A 0 0.76 89
G04-201A 0.76 1.07 86
G04-201A 1.07 3.66 83
G04-201A 3.05 0
G04-201A 3.66 4.57
G04-201A 4.57 0
G04-201C 0 0.91 89
G04-201C 0.76 0
G04-201C 0.91 1.68 83
G04-201C 1.68 2.74
G04-201C 2.74 4.27 83
G04-201C 4.27 5.79
G04-201C 5.79 0

E F
-------- ----- -----
G04-201A 1.5
G04-201A 2.2
G04-201A 3.0
G04-201C 0.6
G04-201C 1.5
G04-201C 3.

here is what i want to do with each row in column 'g

- match column 'e' with column 'a
- match column 'f' such that it is in between column 'b'
and 'c
- if the above conditions are true, return the value from
column 'd' to column 'g

example, 5th row in column 'e' and 'f
'e' = G04-201C, 'f' = 1.5
therefore, returns 830 from column 'd

thx in advance

 

[Guest]

thx. guy. it works!!!
and i picked jason's solution. =P

 

[skyduck]

thx. je. but it all returns 0. and what are the two minus
signs for?

 

[Guest]

thx. jason, this works great.

-----Original Message-----
=INDEX($D$1:$D$13,MATCH(1,(F1>=$B$1:$B$13)* (F1<=$C$1:$C$13)
*(E1=$A$1:$A$13),0))

Array-entered (press ctrl/shift/enter).

HTH
Jason
Atlanta, GA

.

 

[JE McGimpsey]

Works for me with the data you posted.

One minus sign converts the boolean (TRUE/FALSE) array to numeric
(-1/0), the other converts the -1s to 1s.