Checking a cell against a named range

[Guest]

Hello Guys and Girls,

A have a issue with named ranges. I have a named range "TestRange", with the
values "OK", "NOK".

I'm trying to test a cell against this range, so I am using something like
this:

=IF(B8=TestRange;0;1)

But checking a cell against all values within the named range doesn't seem
to work as i expected...

is there another way to implement this behaviour? Or am I doing this
completely wrong?

 

[Bob Phillips]

=--ISNUMBER(MATCH(B8;TestRange;0))

should work

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Leo Heuser]

Hello Guys and Girls,

A have a issue with named ranges. I have a named range "TestRange", with
the
values "OK", "NOK".

I'm trying to test a cell against this range, so I am using something like
this:

=IF(B8=TestRange;0;1)

But checking a cell against all values within the named range doesn't seem
to work as i expected...

is there another way to implement this behaviour? Or am I doing this
completely wrong?

Hello

One way, for a 1- or 2-dimensional TestRange:

For result True or False

=SUMPRODUCT((B8=TestRange)+0)<1

For result 0 or 1

=(SUMPRODUCT((B8=TestRange)+0)<1)+0

 

[Guest]

=--(COUNTIF(TestRange,B8)>0)

 

[Guest]

OK, so the past few days i've been trying all suggestions from you guys...
unfortunately it doesn't work as it should...

There are two things that keep cumming up... First of all only the "Value if
untrue" keeps on popping up, although the checked cell value is within the
named range, so in this case the "value if true" should show up... When i
edit the formula, I can actually see that Excel 2007 recognizes the formula,
because he surrounds the range with a colored box.

Also, all formulas mentioned here keep on giving me some kind of error...

Let me again explain what i need:

I have a list with these items: "vb", "FD", "VFD", "VA", "VB", "JV", "ELF",
etc..."

If we want to check a specific cell against one of the above values, we are
using this form of formula:

=(IF(OR(K64="TK";K64="VA";K64="VB";K64="VC";K64="ELF";K64="JV"... etc))

I thought I could use "named ranges", to shorten the formula significantly.
However this doesn't seem to work as it should, or I am doing something
completely wrong.

A few formulas i used to do some testing:

I have a few items put up in a named range named "TestRange", and i have
tried to use the following formulas aside from the ones in the replies,
without success:

=IF(ISTEXT(EQUALS(D2;TestRange));OK;NOK)
=ALS(EQUALS(D8;TestRange);"OK";"NOK")

 

[Bob Phillips]

Any of the options provided should work. Which language are you working in?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob,

I am using the Dutch version, as I reside in Belgium . Although I think
I'm able to extract the dutch keywords: IF means ALS in Dutch.. and so on,
but it seems i am able to use some in dutch, some in english. If i use:

=ISNUMBER(MATCH(B8;TestRange;0)) - this gives me: name not valid

This one causes the problem with "MATCH" I guess... i tried "GELIJK" in
dutch, but that one doesn't seem to match up... :-(

I also tried:

=ALS(GELIJK(C3;TestRange);"OK";"NOK")

This one gives me only OK when the value matches on the same row: so A3 is
BOB and C3 is also BOB, then it gives me OK. If i change it into something
else, it becomes NOK.


A1:A5 = named range "TestRange":

A B C D
1 Memento Bob =IF(EQUALS(C1;TestRange);"OK";"NOK")
=OK
2 Bob Marc =IF(EQUALS(C1;TestRange);"OK";"NOK")
=NOK
3 Leo TM
4 TM
5 John

So... Cel D1 should give me the value "OK" if Bob falls within the range
"TestRange". Somehow it just gives me the error "name not valid". However, if
i click on the name "TestRange" in the formula bar I see it recognizes the
name because Excel surrounds it with a colored box when i select it...

If I can manage to get this working with your help guys, that would be
amazing.

I also noted there are alot of questions relating to this kind of issue, so
I'm guessing that this kind of "functionality" is quite tricky.

Anyway. already thanks for all the help and advise, but i'm not going to
give up on this one!

BTW: I am using the Dutch version of Excel 2007.

With regards,

Memento

 

[Guest]

Okay, I've got a result here Bob:

I've used:

=ALS(VERGELIJKEN(C3;TestRange;0);"OK";"NOK")

I guess this would:

=IF(COMPARE(C3;TestRange;0);"OK";"NOK")

One slight problem though:

When i use another value, one that is not in the list, it gives me: #N/B or
unknown value. Is there a way to get rid of this one..

Thanks for all the advice already guys

 

[Guest]

=ALS(ISNB(VERGELIJKEN(C3;TestRange;0)"0");"OK";"NOK")

In English:

=IF(ISNB(COMPARE(C3;TestRange;0)"0");"OK";"NOK")

Doesn't work also, the placing of the ISNB seems to be wrong in this one.