Excel Lookups
 

I have a large table of data in which I need to populate a certain
row. Here is an example.
Master table
Price
Nuts
Nuts
Bolts
Bolts
Bolts
Screws
Screws

Price Table
Nuts 1.00
Bolts .50
Screws .25

The price column in the master table is empty and I would like to fill
it in by referencing the price table. Does anyone know how I could do
this? Thanks.
Rory

Excel Lookups
 

Hi
use the following formula in cell B1 of your master sheet:
=VLOOKUP(A1,'price sheet'!$A$1:$B$20,2,0)
and copy this formula down

-----Original Message-----
I have a large table of data in which I need to populate
a certain
row. Here is an example.
Master table
Price
Nuts
Nuts
Bolts
Bolts
Bolts
Screws
Screws

Price Table
Nuts 1.00
Bolts .50
Screws .25

The price column in the master table is empty and I would
like to fill
it in by referencing the price table. Does anyone know
how I could do
this? Thanks.
Rory
.

Excel Lookups
 

Rory,

I like using Named Ranges. I try to design my Tables in
Excel using the following rules

List/table is isolated from other data
List/table is contiguous (no empty columns/rows)
List/Table has ONE ROW of unique headings

Under these conditions I use the CurrentRegion Icon to
SELECT the entire list/table and then Insert/Name/Create-
Create names in TOP row.

I then use column range names in all my worksheet
formulas and in my VBA code.

Sub LoadMasterTable
Dim r as range, sItem as string, ptr as variant
Dim wsMaster as worksheet, wsPrice as worksheet
Set wsMaster = Sheets("Master Table")
Set wsPrice = Sheets("Price Table")
With wsMaster
For Each r in .Range(.Cells(2, 1), .Cells(2, 1).End
(xlDown))
With r
sItem = .Value
ptr = application.match(sItem, wsPrice .Range
("Price_Table_Item"),0)
If Not IsError(ptr) Then
.Offset(0, 1).Value = application.index(range
("Price_Table_Price"),ptr, 1)
End If
End with
Next
End With
End Sub

Now I have a question for you.

Why multiple rows per Item in your Master Table?

SkipVought

-----Original Message-----
I have a large table of data in which I need to populate
a certain
row. Here is an example.
Master table
Price
Nuts
Nuts
Bolts
Bolts
Bolts
Screws
Screws

Price Table
Nuts 1.00
Bolts .50
Screws .25

The price column in the master table is empty and I
would like to fill
it in by referencing the price table. Does anyone know
how I could do
this? Thanks.
Rory
.

Excel Lookups
 

Thanks a bunch Frank! That works perfectly.
Rory