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

[Jimmy]

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

 

[Pete_UK]

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

 

[Bob Phillips]

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]

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

 

[Jimmy]

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.