Alternative formula to the vlookup formula?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Victor - 08 May 2008 22:46 GMT

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

Reply to this Message

Ted M H - 09 May 2008 01:29 GMT

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...

> I have data arranged like this:
> a b c d
[quoted text clipped - 23 lines]
>
> Victor

Reply to this Message

Victor - 12 May 2008 16:38 GMT

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.

> Hi Victor,
>
[quoted text clipped - 47 lines]
> >
> > Victor

Reply to this Message