Alternative formula to the vlookup formula?

[Victor]

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

 

[Ted M H]

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]

Hi Ted.

Thanks for your help. There wasn't time to use your suggestion since we
would like to push forward with the spreadsheet so we used another "cheat" to
get our totals. I'll keep your information on hand for the next time we
retool the spreadsheet though.

What we did was make two subtotals and then added them to get a final total.

Thanks again for your help.