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.