Set a named range for each index column and then call the following function
with

lookupRange(range("SheetName!RangeName"),valueYouA reLookingFor,OffsetColumn)


Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As
Presets) As Variant

On Error Resume Next

Dim mr As Range

Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole,
SearchDirection:=xlNext, SearchOrder:=xlByColumns)

lookupRange = mr.Offset(0, col - 1).Value


End Function


"Craig McK" wrote in message
...
I am trying to build a quotation package as follows, and could do with some
(lots) of help trying to get it working

I have an excel book with a number of sheets (PRICES), each sheet contains
the item number and in the adjacent cell the price.
What I want to do is enter the part number in the quotation page, then
have
a macro go and lookup (PRICES) to find the item, then return the value in
the
adjacent cell. This should continue until it reaches the end on the items
to
be priced. Note that the item could be in any one of 4 sheets, but will
only
appear once.

I am new to VBA and totally lost, any help at all would be good

Thanks