I think you would need two named ranges for the array function, one for the
low values and another for the high values. A range name seems fine in the
UDF I provided.

{=IF((SUM((H1=LowValues)*(H1<=HighValues))0), "found","not found")}

(array entered)

Incidentally, {=(SUM((H1=LowValues)*(H1<=HighValues)))} gives you 1 or 0
rather than TRUE or FALSE

Can't understand why the UDF is not working for you. If ARange refers only
to the header row, that would result in all the returned values being zero.

For example, ARange refers to =Sheet3!$C$1:$D$1

Regards


"Martin" wrote in message
...
In both cases, I am using a Range Name for the lookup table and a single
cell
reference for the lookup value.

The formula I am using for the function is: =IsBetween(ARANGE, h10) where
ARANGE is the name for my lookup table.

The array formula is essentially the same as posted earlier except that I
am
using a Range Name for the lookup table and using an appropriate cell
reference.

I am at a complete loss. I guess I may end up doing this the hard way.


"Trevor Shuttleworth" wrote:

In both cases we would need to know what range contains the lookup table
and
what cell contains the lookup value. And, ideally the data for the table
and the lookup value. And the actual formula you are using in both
cases.

Both methods worked for me but the case sensitivity makes a difference.
My
function gives 0 or not found for lower case b but the array function
gives
TRUE or found. Your choice.

With regard to the table, that is not a problem for either Randy's array
function or my UDF.

I suspect that you haven't defined the range correctly or perhaps you
have
not made the rows absolute ($) ... in fact, if I were a betting man, I'd
put
money on it.

Regards


"Martin" wrote in message
...
Actually, my data is not case sensitive, but I can deal with that.

I have been trying your function and the array formula and have
problems
with both.
--When I use your function, it is always returning a 0.
--when I use the array formula, it is working in some instances, but
not
others.

This is a rather quick review. I am working with these more. I am
thinking
I may have to break my lookup table between numeric and text to solve
this.



"Trevor Shuttleworth" wrote:

{=IF((SUM((F1=C$2:C$5)*(F1<=D$2:D$5))0), "found","not found")}

Array Entered again

Note that this is *not* case sensitive, whereas your data appears to
be.
Is
that important ?

The function I wrote treats "b" and "B" differently ... do you care ?

Regards

Trevor


"Martin" wrote in message
...
I tried your suggestion and it seems to do what I want. I tried to
use
this
formula as part of an IF statement and get an error. I included the
curly
braces around the array formula inside the IF

Can this array formula be used inside an IF?

Thanks.

"Randy Harmelink" wrote:

You could just use an array formula:

=(SUM((J9=G$10:G$13)*(J9<=H$10:H$13))0)

.....where J9 is the value you are testing and G10:G13 are the
lower
table values and H10:H13 are the upper table values.

Martin wrote:
I am in the process of trying to create a Function that will take
a
value and
determine if it appears in a table of ranges. For example, my
table
of
ranges may be:
Lower Upper
10000 15000
30000 35000
A C
g i

If I use a value of 11000, my function would return a 1 since it
is
in
one
of the ranges. A value of d would return a 0.