Try something like this:

=IF(ISNA(MATCH(B3,ccode,0)),"East","West")

If your number codes are proper numbers, then B3 also has to be a
number to get a match.

Similarly, if your number codes are text values that look like a
number, then B3 must also be a text value - or you can do something
like this to make sure that it is comparing like with like:

=IF(ISNA(MATCH(B3&"",ccode,0)),"East","West")

Hope this helps.

Pete

On Jun 6, 12:17*am, Jimmy wrote:
Earlier I asked the below questions and got the below response. *I'm just now
realizing that the solution written below only worked for those items in the
range that were made up of 3 numbers. *However, it did not work for the codes
that were made up of 3 letters. *So for instance, it was able to dispay a
correct West or East designation if I were looking for the code "500". *
However, if the formula was looking in the range for the code "ABC" and it
was actually in that range, I still received a false designation (i.e. it
returned a 0 value) which I had designated to desplay the string "East".

Any thoughts on how to make the formula work with both 3 character codes
that contain both numbers and letters or a mixture of the two?

Thanks for your help,

Jimmy



"Ron Coderre" wrote:

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

"Jimmy" wrote in message
...
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 syntax of this formula is incorrect because I
receive a
"#Name?" error.

Any thoughts?- Hide quoted text -

- Show quoted text -