Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Lookup Table

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a text file I want to import into Excel. One of the data sets has a code for the actual data (i.e., A for apple, B for bananna, C for Grapefruit, D for Pineapple, .... U for whatever).

    Is there an easy formula to covert the A, B, C, etc to the actual data value? The only way I know how to do so is:

    If (A2="A","Apple",if(A2="B","Bananna"))

    This will be rather cumbersome for letters A through U!!

    Thanks

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,593
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-25 07:47, agraham216 wrote:
    I have a text file I want to import into Excel. One of the data sets has a code for the actual data (i.e., A for apple, B for bananna, C for Grapefruit, D for Pineapple, .... U for whatever).

    Is there an easy formula to covert the A, B, C, etc to the actual data value? The only way I know how to do so is:

    If (A2="A","Apple",if(A2="B","Bananna"))

    This will be rather cumbersome for letters A through U!!

    Thanks
    Make a 2-column list consisting of letters and the values that associated with them:

    A Apple
    B Bananana
    etc

    Select all of the cells of this 2-column list, go to the Name Box on the Formula Bar, type CTable (from conversion table), and hit enter.

    Then use the following formula:

    =VLOOKUP(A1,CTable,2,0)

    to convert the value in A1 to the value it's associated with.



    [ This Message was edited by: Aladin Akyurek on 2002-03-25 08:18 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Leiden, The Netherlands
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use the Lookup function for this:

    =LOOKUP(A1,$D1:$E21,2,FALSE)

    Where A1 contains the code you want to look up (A, B, C, etc.) and $D1:$E21 contains a conversion table (the letters A through U in column D, and the words "Apple" through "Underground" in column E).

    Marc

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, I would say a lookup table would be the best option. Let's say you put the letters in Column A and the correspoding name in Column B.

    The you could use:


    =VLOOKUP(D2,A1:20,2,False)





  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Leiden, The Netherlands
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's right, I meant VLOOKUP!

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Worked Great!!! Thanks!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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