Re: 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" <Jimmy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7CAC01C3-9949-4FF3-9764-D8CB0EB658BA@xxxxxxxxxxxxxxxx
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" <Jimmy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4D8A9B76-E329-423F-B7E6-90708A7E2ADE@xxxxxxxxxxxxxxxx
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?





.

Relevant Pages

  • Re: HOW TO REPLACE TEXT AUTOMATICALLY
    ... ABC import and export company ... Start a new workbook--its only purpose is to hold the macro and the list of ... there're some words in the cell are part of the text. ... IS IT BECAUSE MY VERSION OF EXCEL ...
    (microsoft.public.excel.misc)
  • Re: HOW TO REPLACE TEXT AUTOMATICALLY
    ... ABC import and export company ... Start a new workbook--its only purpose is to hold the macro and the list of ... there're some words in the cell are part of the text. ... IS IT BECAUSE MY VERSION OF EXCEL ...
    (microsoft.public.excel.misc)
  • Re: Help with IF
    ... *Commit array formula with Ctrl+Shift+Enter, ... The cell being tested will not contain exactly "abc", "def", etc, but more ... I need to capture text from cell D2 if cell D1 contains "abc", otherwise use the text from D1. ... I would like to expand this now so that if D1 contains either "abc", "def", ...
    (microsoft.public.excel.misc)
  • RE: vlookup column based on user input
    ... Item ABC DEF XYZ ... Select the cell with 2. ... Item Source Cost ...
    (microsoft.public.excel.worksheet.functions)
  • Tooltips or Comment looking up a reference
    ... into a spreadsheet and have the ability to then hover over that cell ... comment if it recognises that the abbr. ... abc = alphabet ... the ability to hover to read what they mean. ...
    (microsoft.public.excel)
Quantcast