finding is a value is betwen a range of values
 

I have several Colors in ColumA, on column B I have the lowest value and on
column C I have the highest.. I can have same color several times with
different range values.... how do I find if a given value meets one of those
criterias?

Col A Col B Col C
Blue 1 100
Blue 150 400
Yellow 200 300
Blue 500 750

I want to seach (Blue 200) and get a true value (is on range blue 150-400)
I search for (Blue 800) and get a false value (is not on any valid range)

Thanks

finding is a value is betwen a range of values
 

Try this...

E2 = blue
F2 = some number

Try this array formula** :

=ISNUMBER(MATCH(1,(A2:A5=E2)*(B2:B5<=F2)*(C2:C5=F 2),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Alberto Ast" wrote in message
...
I have several Colors in ColumA, on column B I have the lowest value and on
column C I have the highest.. I can have same color several times with
different range values.... how do I find if a given value meets one of
those
criterias?

Col A Col B Col C
Blue 1 100
Blue 150 400
Yellow 200 300
Blue 500 750

I want to seach (Blue 200) and get a true value (is on range blue 150-400)
I search for (Blue 800) and get a false value (is not on any valid range)

Thanks

finding is a value is betwen a range of values
 

Not sure how it worked but it did great.... I will have to learn more about
it in the future.... for now I think it meet my needs... I will go to the
complete file I need to update with the information you gave me.
Thanks

"Alberto Ast" wrote:

I have several Colors in ColumA, on column B I have the lowest value and on
column C I have the highest.. I can have same color several times with
different range values.... how do I find if a given value meets one of those
criterias?

Col A Col B Col C
Blue 1 100
Blue 150 400
Yellow 200 300
Blue 500 750

I want to seach (Blue 200) and get a true value (is on range blue 150-400)
I search for (Blue 800) and get a false value (is not on any valid range)

Thanks

finding is a value is betwen a range of values
 

T Valko's formula is clever but it is an array formula
This one is a 'simple' formula - just commit it with ENTER
=SUMPRODUCT((A2:A5=E2)*(B2:B5<=F2)*(C2:C5=F2))0
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Alberto Ast" wrote in message
...
I have several Colors in ColumA, on column B I have the lowest value and
on
column C I have the highest.. I can have same color several times with
different range values.... how do I find if a given value meets one of
those
criterias?

Col A Col B Col C
Blue 1 100
Blue 150 400
Yellow 200 300
Blue 500 750

I want to seach (Blue 200) and get a true value (is on range blue 150-400)
I search for (Blue 800) and get a false value (is not on any valid range)

Thanks

finding is a value is betwen a range of values
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Alberto Ast" wrote in message
...
Not sure how it worked but it did great.... I will have to learn more
about
it in the future.... for now I think it meet my needs... I will go to the
complete file I need to update with the information you gave me.
Thanks

"Alberto Ast" wrote:

I have several Colors in ColumA, on column B I have the lowest value and
on
column C I have the highest.. I can have same color several times with
different range values.... how do I find if a given value meets one of
those
criterias?

Col A Col B Col C
Blue 1 100
Blue 150 400
Yellow 200 300
Blue 500 750

I want to seach (Blue 200) and get a true value (is on range blue
150-400)
I search for (Blue 800) and get a false value (is not on any valid range)

Thanks

finding is a value is betwen a range of values
 

This is great... work as you say simple... people around me say I know a lot
of excel but it is because I am not near the experts... I really appreciate
all your help.

"Bernard Liengme" wrote:

T Valko's formula is clever but it is an array formula
This one is a 'simple' formula - just commit it with ENTER
=SUMPRODUCT((A2:A5=E2)*(B2:B5<=F2)*(C2:C5=F2))0
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Alberto Ast" wrote in message
...
I have several Colors in ColumA, on column B I have the lowest value and
on
column C I have the highest.. I can have same color several times with
different range values.... how do I find if a given value meets one of
those
criterias?

Col A Col B Col C
Blue 1 100
Blue 150 400
Yellow 200 300
Blue 500 750

I want to seach (Blue 200) and get a true value (is on range blue 150-400)
I search for (Blue 800) and get a false value (is not on any valid range)

Thanks