Hi Victor,

It's pretty easy to solve if you can cheat a bit. If you put a VLOOKUP
formula in a row directly above the assemply numbers and get the prices there
then you don't have to use the lookup function in your total formula. Here's
a way that would work IF you are OK with that idea.

In C3 (where row 4 has your assembly numbers) enter
VLOOKUP((C4,$A$5:$B$10,2,FALSE)
Probably would be best to use a named range for the Table-array range rather
than the cell references
Copy that formula across all the columns in which you have assemblies--in
your example that would be C3:E3, I think

Then use this formula for your totals in column F: In cell F5 (where row 5
is the row just below the assembly numbers and contains your first
part--abc1) enter
=B5+SUMPRODUCT(C$3:E$3,C5:E5)
and copy that formula down into all the rows for which you have parts.

I'm sure there's a way to do this without the VLOOKUP row, but I can't think
of it off the top of my head. Of course you can hide the VLOOKUP row so only
you know it's there...

"Victor" wrote:

I have data arranged like this:
a b c d
e f
assembly
Part No. price xyz1 xyz2 xyz3 TOTAL
abc1 1 3 1 xxx
abc2 2 2 1 xxx
abc3 3 1 3 xxx
xyz1 .4
xyz2 .5
xyz3 .6


Part abc1 is assembled with 3 pieces of xyz1 and 1 piece of xyz2. I would
like to have the cells under "TOTAL" give the total price of that part
including the assembly components so for part no. abc1, I want the price to
show $2.70, part no. abc2, to show $3.6 and part no. abc3 to show 5.2.

I am currently using a =sum(vlookup*c3, vlookup*d3, vlookup*e3) formula
which works great except that there are so many assembly items that I cannot
add any more functions to the cell. Is there a different forumla which would
allow me to find the total price?

If you need additional information, please let me know.

Thanks,

Victor