Compare to a range of values using "from/to"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to compare values from one set of data to a range of data - without
having to type in each of the individual data range values to compare to.
For example:
look for 1001000 (Col A Value)

in range (Col B Values)
from 0000000
to 1999999

without having to use data fil to actually show each individual value, like:
0000000,0000001,0000002....all the way to 1999999

Thank you,
Lisa
 
If you split your column B into two separate columns containing the
lower and upper limits respectively, you will be able to use simple IF
statements to see if your test value is greater than the lower or less
than the upper. You cab then combine these two in a further if
statement which tests if they are both true. If this is the case, then
your test vakue lies between the limits.
 
=VLOOKUP(A1,B:B,1,TRUE)

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the
largest value that is less than or equal to lookup_value.

hth
Vaya con Dios,
Chuck, CABGx3
 
This formula
=AND(A1>=MIN(B:B),A1<=MAX(B:B))
will tell you if the value in A1 is between the highest and lowest
values in column B. But I don't understand how your values might be
00001- all those leading zeros indicate specialized formatting or text
entries. This formula works with specialized formatting, but would
need adjusting if your entries are text.
 
the greater/less than in two separate columns, great!
that's brilliant.
thanks so much.
Lisa O.
 
Back
Top