Lookup table???

[danielle]

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?

[Aladin Akyurek]

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

[danielle]

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 ]

[Aladin Akyurek]

On 2002-03-17 06:58, danielle wrote:

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