finding is a value is betwen a range of values

  • Thread starter Thread starter Alberto Ast
  • Start date Start date
A

Alberto Ast

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
 
Try this...

E2 = blue
F2 = some number

Try this array formula** :

=ISNUMBER(MATCH(1,(A2:A5=E2)*(B2:B5<=F2)*(C2:C5>=F2),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.
 
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
 
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
 
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.
 
Back
Top