Looking up a value within a range of 3 letter or number codes...

J

Jimmy

Hello,

I'm trying to determine the best way to return a value or string using an
if-then statement. The current formula I have looks like this:

=IF(B3=ccode,"West","East")

So basically, if whatever is in the cell B3 (let's say its "ABC") is also in
the range named "ccode", then it returns the string "West".

However, I know the sytax of this formula is incorrect because I receive a
"#Name?" error.

Any thoughts?
 
R

Ron Coderre

Try this:
=IF(COUNTIF(ccode,B3),"West","East")

Is that something you can work with?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel
 
J

Jimmy

Ron,

Thanks for your help. It worked. I have another question though. When
nested "COUNTIF" formula runs, it returns either a 1 or a 0. The 1 is
returned if the cell contents are also in the range and a 0 if it is not in
the range. How does the if - then formula know that a 1 is a true outcome
and a 0 is a false outcome?

Jimmy
 
R

Ron Coderre

Hi, Jimmy

The first part of the IF function works like this:
It interprets zero (0) as FALSE
and all other numbers (positive or negative) as TRUE.

Examples:
=IF(0,"Is true","Is false").....returns: Is false
=IF(-6,"Is true","Is false").....returns: Is false
=IF(32,"Is true","Is false").....returns: Is true
=IF(0.0023,"Is true","Is false").....returns: Is true

Does that help?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel
 
J

Jimmy

I'm a little confused. You say that all other numbers (positive or negative)
will display as true, yet you have below:

=IF(-6,"Is true","Is false").....returns: Is false

Why would this function return as false?

Jimmy
 
D

David Biddulph

For me, the formula =IF(-6,"Is true","Is false") returns Is true, so Ron's
description was correct, but his example wasn't.
 
R

Ron Coderre

Sorry....typo!

It should be:
=IF(-6,"Is true","Is false").....returns: Is true

Thanks for catching that and letting me know.

Regards,

Ron
Microsoft MVP - Excel
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Looking up a value within a range of 3 letter or number codes 4
trouble with (complicated) vlookup searching another workbook 2
SUMIF data that falls within a range 3
IF(AND(OR function returns #VALUE! error 3
How to check if the values in a range of cells are greater than 0 3
Return a value if a range of cells contain text 2
Search a string in a cell for a list of 3 letter codes 3
Looking up a returned value defining a named range 2

Top