Summing totals based on first 3 characters

Hello Excel World,

I am trying to make a dynamic invoice for a vendor of mine. I want them to be able to type in the quantities and part numbers in the invoice and have the invoice total the items automatically.

If you look at my demo file you will see it is very simple.

Basically I want the total column to fill in and sum up the QTY automatically based on the values in the legend on the right of the sheet.

So for C2 it should look at the first 3 characters in B2 and then reference the legend and see that everything that starts with 131 should be 0.98 cents a piece and multiply the Qty of 50 by the 0.98.

Please let me know if this information is too vague? I am trying not to over complicate. I just want to know how to cross reference and sum these up.

Best Regards,
G Dapp

Originally Posted by martindwilson

for your example in c2
=VLOOKUP(LEFT(B2,3)+0,$G$2:$H$5,2,FALSE)*A2

WOW! That was fast! Thank you so much!

Sorry. same solution

ory182003, Good evening.

Try this one:

C2 --> =A2*(VLOOKUP(VALUE(LEFT(B2,3)),$G$2:$H$5,2,FALSE))

I hope it helps.

Hi There again,

There will be times when the invoice is not filled out all the way. Can you modify the code so the empty cells don't display the error (#VALUE!) ?

Thanks again!

Thank you!

You guys are amazing....really appreciate it!