Lookup table???

danielle

New Member
Joined
Mar 16, 2002
Messages
10
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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top