Lookup table???
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Lookup table???

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Melbourne, Victoria
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi...I was wondering if anyone would be able to help me with this excel dilemma:

    I have a stock inventory set up as a table with the table headings set as Item Code, Description, Cost, Mark-Up, Ex-GST, GST, RRP. Now I have been asked to set this table up as a lookup table.

    I have no idea what a lookup table is. Could anyone explain?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,872
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-17 02:37, danielle wrote:
    Hi...I was wondering if anyone would be able to help me with this excel dilemma:

    I have a stock inventory set up as a table with the table headings set as Item Code, Description, Cost, Mark-Up, Ex-GST, GST, RRP. Now I have been asked to set this table up as a lookup table.

    I have no idea what a lookup table is. Could anyone explain?
    Danielle,

    What you already set up is a "lookup" table which can be fed to a worksheet function as VLOOKUP. Suppose that this table is in A1:F100 in a sheet named Inventory.

    You should be able to retrieve for a given item code the mark-up associated with that item anywhere in your workbook where you need it. The formula for that then be something like:

    =VLOOKUP(D1,Inventory!A2:F100,4,0)

    where D1 is a cell in a different worksheet and holds an item code. The 0 in VLOOKUP is the same as FALSE, which indicates that you want an exact match between the value in D1 and the values in A2:A100 in Inventory.

    Aladin

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Melbourne, Victoria
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-17 02:47, Aladin Akyurek wrote:

    Danielle,

    What you already set up is a "lookup" table which can be fed to a worksheet function as VLOOKUP. Suppose that this table is in A1:F100 in a sheet named Inventory.

    You should be able to retrieve for a given item code the mark-up associated with that item anywhere in your workbook where you need it. The formula for that then be something like:

    =VLOOKUP(D1,Inventory!A2:F100,4,0)

    where D1 is a cell in a different worksheet and holds an item code. The 0 in VLOOKUP is the same as FALSE, which indicates that you want an exact match between the value in D1 and the values in A2:A100 in Inventory.

    Aladin
    Thanks for your quick response Aladin!

    But my issue is if I were to put that VLOOKUP function into the cell then I would only be looking up one particular cell.

    Imagine an invoice that had 5 columns: Quantity, Item Code, Description, Unit Price, Total Price. In the Item Code column I want to be able to enter an item code and have the description and unit price pop up into the corresponding cells.

    Now there are 15 different item codes. Is it possible to put all of these into the one VLOOKUP function formula? And will this function automayically enter the item description and unit price into the corresponding cells?

    Also how would I data entry into that cell to put into the item code I require without overwriting the function?

    [ This Message was edited by: danielle on 2002-03-17 07:06 ]

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,872
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-03-17 06:58, danielle wrote:
    On 2002-03-17 02:47, Aladin Akyurek wrote:

    Danielle,

    What you already set up is a "lookup" table which can be fed to a worksheet function as VLOOKUP. Suppose that this table is in A1:F100 in a sheet named Inventory.

    You should be able to retrieve for a given item code the mark-up associated with that item anywhere in your workbook where you need it. The formula for that then be something like:

    =VLOOKUP(D1,Inventory!A2:F100,4,0)

    where D1 is a cell in a different worksheet and holds an item code. The 0 in VLOOKUP is the same as FALSE, which indicates that you want an exact match between the value in D1 and the values in A2:A100 in Inventory.

    Aladin
    Thanks for your quick response Aladin!

    But my issue is if I were to put that VLOOKUP function into the cell then I would only be looking up one particular cell.

    Imagine an invoice that had 5 columns: Quantity, Item Code, Description, Unit Price, Total Price. In the Item Code column I want to be able to enter an item code and have the description and unit price pop up into the corresponding cells.

    Now there are 15 different item codes. Is it possible to put all of these into the one VLOOKUP function formula? And will this function automayically enter the item description and unit price into the corresponding cells?
    Danielle,

    VLOOKUP fits your situation perfectly.

    Lets say that the table is Inventory!A2:G100, with

    A2:A100 housing item codes (Item Code),
    B2:B100 housing descriptions (Description),
    C2:C100 housing Cost,
    D2:D100 housing mark-ups (Mark-Up),
    E2:E100 housing Ex-GST,
    F2:F100 GST, and finally
    G2:G100 RRP.

    As far as VLOOKUP concerned this table has 7 'columns'.


    Presupposing the above table and supposing that you have your invoice in some worksheet with item codes, say, in E5:E15 with quantities in D5:D15.

    You want the descriptions to appear in F5:F15, the prices in G5:G15, along with totals that must be computed in H5:H15.

    In F5 enter:

    =VLOOKUP(E5,Inventory!A2:G100,2,0) [ 2 refers to the Description column in the table ]

    In G5 enter:

    =VLOOKUP(E5,Inventory!A2:G100,3,0) [ 3 refers to the Cost column in the table ]

    If Cost is not Unit Price, but Cost times Mark-up is, you need either to compute Unit Price here G5 or precompute it in an additional column within the table.

    In H5 enter: =D5*G5

    Select F5:H5 and copy down as far as needed.

    Everything you need show up automatically in the invoice.

    Aladin


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
  •  

 

 
DMCA.com