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

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?

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

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 -

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

If the cell contains just "ABC" that formula will work

If it is a substring, try

=IF(SUMPRODUCT(--(ISNUMBER(FIND(B3,ccode)))),"West","East")

--
HTH

Bob

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

"Jimmy" wrote in message
...
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?

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

Unfortunately neither of those formulas worked. It's too bad I can't attach
the excel file to this message. But just to let you know, I got the same
result as the prior tries. Only the 3 letter codes that were all numbers and
that were located in the range retruned "West" values. Any other ideas?

Thanks to the both of you for your help,

Jimmy

"Bob Phillips" wrote:

If the cell contains just "ABC" that formula will work

If it is a substring, try

=IF(SUMPRODUCT(--(ISNUMBER(FIND(B3,ccode)))),"West","East")

--
HTH

Bob

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

"Jimmy" wrote in message
...
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?

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

NEVERMIND.... I figured out what the problem was. Just to let you know, all
the suggested formulas worked. The problem I just realized was that the 3
letter codes had trailing spaces behind them so while the formula was able to
remove the extra spaces of the numbered codes, it wasn't able to do that for
the string codes. With that said, thanks again for your help.

"Jimmy" wrote:

Unfortunately neither of those formulas worked. It's too bad I can't attach
the excel file to this message. But just to let you know, I got the same
result as the prior tries. Only the 3 letter codes that were all numbers and
that were located in the range retruned "West" values. Any other ideas?

Thanks to the both of you for your help,

Jimmy

"Bob Phillips" wrote:

If the cell contains just "ABC" that formula will work

If it is a substring, try

=IF(SUMPRODUCT(--(ISNUMBER(FIND(B3,ccode)))),"West","East")

--
HTH

Bob

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

"Jimmy" wrote in message
...
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?