Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Return Number based on Text (Excel 2003)

    Hi

    I have a range of text in Col A and want to return a code number based on it.
    I have tries the Lookup but doesn't give the result. Here is the formula I am using

    =LOOKUP(A1,{"None","Heat","Melt","Growth","Anneal" ,"Cool","E_cool"},{6,5,4,3,2,1,0})

    TIA

    regards, francis
    Attached Images Attached Images
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return Number based on Text (Excel 2003)

    I'd create a lookup table in a sheet and use VLOOKUP.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Return Number based on Text (Excel 2003)

    This will do it:
    Jerry

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Return Number based on Text (Excel 2003)

    Here is mu <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> ...
    You could even improve it more by adding list validation to cell A9, so that one could use the in-cell drop-down list to select the text value. So doing the lookup value will not be affected my typo's or spelling.
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return Number based on Text (Excel 2003)

    > mu <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    > not be affected my typo's

    Excellent! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return Number based on Text (Excel 2003)

    Thanks Rudi

    That's a good idea.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return Number based on Text (Excel 2003)

    Hans and Jerry

    Thanks for suggesting this. I wanted to use the Lookup function but not sure why its doesn't works.
    Any idea?

    TIA

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return Number based on Text (Excel 2003)

    The built-in help for the LOOKUP function states explicitly that the values in the second argument must be sorted in ascending order, otherwise LOOKUP won't work correctly. You could have used
    <code>
    =LOOKUP(A1,{"Anneal","Cool","E_cool","Growth","Hea t","Melt","None"},{2,1,0,3,5,4,6})
    </code>
    But still, this formula is difficult to maintain as values are added, removed or changed. It's much better to use a lookup table in a sheet.

  9. #9
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return Number based on Text (Excel 2003)

    The first array in LOOKUP has to be sorted ascending. Try

    =LOOKUP(A1,{"Anneal","Cool","E_cool","Heat","Melt" ,"None"},{3,1,0,4,5,6})

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return Number based on Text (Excel 2003)

    No Growth any more? Must be the credit crisis... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return Number based on Text (Excel 2003)

    Hi Hans,

    Thanks but its still don't works after having sorted the data.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return Number based on Text (Excel 2003)

    Look at Sheet1 in the workbook attached to <post:=750,978>post 750,978</post:> (in the "calculating increasing rates" thread).
    Cell F12 contains a working LOOKUP formula.

  13. #13
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return Number based on Text (Excel 2003)

    That a completely different formula!!

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return Number based on Text (Excel 2003)

    I'm very sorry, I give up. It's exactly the same formula.

  15. #15
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return Number based on Text (Excel 2003)

    Francis,

    The formula that Hans provided in <post#=750,971>post 750,971</post#>:

    =LOOKUP(A1,{"Anneal","Cool","E_cool","Growth","Hea t","Melt","None"},{2,1,0,3,5,4,6})

    is identical to the <working> formula in the workbook at <post#=750,978>post 750,978</post#>, sheet 1 F12:

    =LOOKUP(A1,{"Anneal","Cool","E_cool","Growth","Hea t","Melt","None"},{2,1,0,3,5,4,6})

    Regards

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •