One way to try ..

Assuming this reference table is in Sheet2,
cols A to G, data from row2 down

Product Cost Pack Tax Weight
Orange 1.5 6 0.7 2lbs
Mango 3 12 0.85 4lbs

And in Sheet1, in cols A to G, you have:

User Product Quantity [Pack] [Cost] [Weight] [Tax]
Davis Mango 15
Leslie Orange 24

where Pack, Cost, Weight and Tax are in cols D to G

Try these formulas in:

D2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet 2!C:C,MATCH($B2,Sheet2!$A:
$A,0)))

E2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet 2!B:B,MATCH($B2,Sheet2!$A:
$A,0))*C2)

F2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet 2!E:E,MATCH($B2,Sheet2!$A:
$A,0)))

G2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet 2!D:D,MATCH($B2,Sheet2!$A:
$A,0)))

[ For G2, leave it to you to complete the tax computation, if that's what is
wanted in col G. The formula in G2 just retrieves the tax figs from Sheet2 ]

Select D2:G2,
fill down as many rows as there is data in cols A to C

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Dennis G." wrote in message
...
Max,

Sorry just getting back to you... The underlying logic is:

I have on sheet2 some data -- say:
Product Cost Pack Tax Weight
Orange 1.50 6 .7 2lbs
Mango 3.00 12 .85 4lbs

On Sheet1 I have some data (12,000 rows)
User Product Quantity [Pack] [Cost] [Weight] [Tax]
Davis Mango 15
Leslie Orange 24
etc...

I use the function above to return the values that I need... I hope this
helps.

Thanks
Dennis G.