Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jul 2008
    Location
    San Diego, California, USA
    Posts
    73
    Thanks
    25
    Thanked 0 Times in 0 Posts

    How to create Lookup Tables?

    Is there some special formatting required to create a lookup table? Can I enter data and format the data as a table and then refer to it as a lookup table?

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Are you referring to VLOOKUP and/or HLOOKUP? If so, you can use either with a "block" of data or a more formal table.

    VLOOKUP, for example, needs a value you're trying to find (first argument of VLOOKUP), followed by the range of data in which to look (in the first column of the range), then which column you want returned when the value is found, and, lastly, an indication of whether the data is sort.

    If you mean something other than this, please be more specific.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    For lookups it is the structure not the format that is important.

    Steve

  4. #4
    Star Lounger
    Join Date
    Jul 2008
    Location
    San Diego, California, USA
    Posts
    73
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Iím in school for Excel 2010 but this is not part of an assignment. Iím just curious.

    I have an existing table and was asked to write a formula using nested IFís to calculate employee bonus. No problem, I can do that. The bonus is based upon pay scale rating of 1, 2 or 3 located in column ďIĒ of my existing table. The bonus amounts are given on the same worksheet but not as part of the table in the range of X2:Y4.

    I thought that rather than using nesterd IFís I could convert X2:Y4 into a Lookup Table and then use VLookup to obtain the same answers as the nested IF formula. I could rearrange the X2:Y4 range and use HLookup also.

    My dilemma is that this is a lab class with a program provided by the textbook mfgr. Sometimes Excel will not perform as it would if you opened a new workbook and just began working. The mfgr sometimes modifies Excel for instructional purposeís. Iím trying to figure out if Iím doing something wrong or is it the software?

    I selected the range X2:Y4 and converted it into a table and called it Table8. Then I tried to use VLookup and I get an error #NA. Then I rewrote X2:Y4 to a different location X12:Y14, converted it into a table and tried again-#NA. Then I rewrote X2:Y4 to an existing worksheet, Lookup Tables and tried again-#NA.

    While the answer to my question has nothing to do with what Iím supposed to be doing in the chapter tutorial, I canít let it go. How do I create a functioning Lookup Table?

    I've tried to attach the workbook to this reply so you can see exactly what I'm talking about.

    Thanks for the help
    Kevin
    Attached Files Attached Files

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    From my testing the problem is the bonus table index value (1st column) has text where the Pay Grade column in the table does not.
    This formula: =VLOOKUP("Bonus Pay Grade " &Employee[@[Pay Grade]],$X$2:$Y$4,2)
    fixes that problem.
    vlookupproblem.JPG

    Good luck with your learning experience.
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Star Lounger
    Join Date
    Jul 2008
    Location
    San Diego, California, USA
    Posts
    73
    Thanks
    25
    Thanked 0 Times in 0 Posts
    RG,
    Thanks! Your formula works but I don’t know why. I’m being taught that there should not be spaces in a formula and your’s has a critical space without which the formula won’t work. The space I’m referring to is between the word Grade and the quotation mark.

    The text in cells X2:X4 is what screwed me up, I guess. I edited the text out and this formula now works. =VLOOKUP(I2,$X$2:$Y$4,2,FALSE)

  7. #7
    Star Lounger
    Join Date
    Jul 2008
    Location
    San Diego, California, USA
    Posts
    73
    Thanks
    25
    Thanked 0 Times in 0 Posts
    I don't know where the "thank you" button went so Thanks to kweaver, sdckapr and Retired Geek.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    The spaces you refer to is in a String (between quotes) and is required because the value to be matched has spaces in those places. Basically I'm concatenating the value "Bonus Pay Grade " with the value stored in the table Employee[@[Pay Grade]] to come up with a value that will MATCH the 1st column in the lookup table $X$2:$Y$4. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Star Lounger
    Join Date
    Jul 2008
    Location
    San Diego, California, USA
    Posts
    73
    Thanks
    25
    Thanked 0 Times in 0 Posts
    RG,

    Your way is the correct way and I'm creating a forced solution. For now, I will stay in the shallow end of the pool.

    Thanks again for the help
    Kevin

Posting Permissions

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