# VLookup is not adding correctly?

I have a question that is bugging me. I do not understand all the commas, dots and dashes, who what where or how as much as many of you so if you can answer my question please answer without all the computer lingo so I know what your talking about. But here is my question and a brief summary of my worksheet... This is for a lye calculator. Sheet one is an inventory sheet which breaks down all the oils and fragrances by the ounce and when used individually for each loaf of soap it tells correct materials cost. However,sheet 2 is a lye calculator with list boxes for several oils and fragrances. It gets that information from the inventory sheet. What ever I change on the inventory sheet is reflected in list box. So, while I can change each individual oil by using the list box it will change every thing else correctly. Each oil requires a different amount of lye but at the same time each oil costs differently and the menu is set up to see the changing price as the oils and fragrances are changed. My problem: Vlookup is telling me the correct price for each individual oil UNTIL (I think?) it hits an empty cell. For instance: Rows A1:A4 have an oil in each cell. It adds those up correctly. A5 will be blank (or deleted) but when I go to add an oil to A5 the price goes kablooey and it doesn't add correctly anymore. To see what I am talking about you can view this link. https://www.youtube.com/watch?v=PKlAo... I do not make any money on ads or views so I am not trying to sell anything but it continues to bug me why this is not quite correct. Here is my complete formula in the cell that adds the money.....

```
=IF(ISBLANK(A6),A6="0",((VLOOKUP(A6,MyOils,5,0))*D6))+IF(ISBLANK(A7),A7="0",((VLOOKUP(A7,MyOils,5,0))*D7))+IF(ISBLANK(A8),A8="0",((VLOOKUP(A8,MyOils,5,0))*D8))+IF(ISBLANK(A9),A9="0",((VLOOKUP(A9,MyOils,5,0))*D9))+IF(ISBLANK(A10),A10="0",((VLOOKUP(A10,MyOils,5,0))*D10))+IF(ISBLANK(A11),A11="0",((VLOOKUP(A11,MyOils,5,0))*D11))+IF(ISBLANK(A12),A12="0",((VLOOKUP(A12,MyOils,5,0))*D12))+IF(ISBLANK(A13),A13="0",((VLOOKUP(A13,MyOils,5,0))*D13))+IF(ISBLANK(A14),A14="0",((VLOOKUP(A14,MyOils,5,0))*D14))+IF(ISBLANK(A15),A15="0",((VLOOKUP(A15,MyOils,5,0))*D15))+IF(ISBLANK(A16),A16="0",((VLOOKUP(A16,MyOils,5))*D16))+IF(ISBLANK(A18),A18="0",((VLOOKUP(A18,MyFragrances,4,0))*D18))+SUM(taskCost)+SUM(Packaging)
```

What this means is If(oil#1 == empty) then {oil#1 == 0} Else { Match oil#1 in MyOils and tell me the pricePerOz; pricePerOz X theQuantityUsedInD6} or at least that is what I think it is doing.

Then add this amount to the next oil in the list .

Your help ...

Hi,

it is very difficult to answer such a question without having the sheet. I hope my repair works. Please try this: =SUM(IFERROR(VLOOKUP(A6,MyOils,5,0),0)*D6,IFERROR(VLOOKUP(A7,MyOils,5,0),0)*D7,IFERROR(VLOOKUP(A8,MyOils,5,0),0)*D8,IFERROR(VLOOKUP(A9,MyOils,5,0),0)*D9,IFERROR(VLOOKUP(A10,MyOils,5,0),0)*D10,IFERROR(VLOOKUP(A11,MyOils,5,0),0)*D11,IFERROR(VLOOKUP(A12,MyOils,5,0),0)*D12,IFERROR(VLOOKUP(A13,MyOils,5,0),0)*D13,IFERROR(VLOOKUP(A14,MyOils,5,0),0)*D14,IFERROR(VLOOKUP(A15,MyOils,5,0),0)*D15,IFERROR(VLOOKUP(A16,MyOils,5,0),0)*D16,IFERROR(VLOOKUP(A17,MyOils,5,0),0)*D17,taskCost,Packaging)

No, your attempt is worse because there's a character limit in a comment and the formula expression is even different from what you wrote before. You may delete the comment. I edited your question to format the code as code.

Please attach a sample document with (redacted) data. Our crystal ball is broken. Edit your question to attach a document.

Or very easy: =Sum(SUMPRODUCT(IFERROR(VLOOKUP(A6:A17,MyOils,5,0),0),D6:D17),taskCost,Packaging)

That works out very nicely with no problems. It puzzles me why there isn't a multiplier by anything but yet it works versus my version VLOOKUP(A10,MyOils,5,0))*D10 Yours is also somehow doing the same thing and so far I am not getting any rounding errors or anything!

How would I also include the following in your formula? VLOOKUP(A18,MyFragrances,4,0))*D18

Still blindfolded, or perhaps I am really going blind to this subject. Sorry!

I see the attachment, but find no explanation of the difference between expected and calculated figures.

Did you upload a file containing deviant results, or do you expect us to experiment with this until we find a deviation?

Most of us do not make soap, and we do not have an intimate knowledge of the calculation model you have built. We also do not know for sure how you determined the expected result and which calculated result you compared it to. It will take significant time (possibly more time than what you have spent in creating it) studying your work to get a full understanding of it.

Please: upload a

completed calculation(or three) which looks wrong, and tell uswhatis wrong with it. (What did you expect?) When we know where to start and what ...(more)Ok, I deleted the old link and uploaded a new one with a completed chart to see the actual results. The chart is calculated 3 ways. By calculator, By using the menu on the Testing tab and by using the Inventory tab. Only the money portions are concerning. everything else works well. I also explained a little more how everything works on another comment I made a few minutes ago.

Is there something simple for examination, like

"In cell E5 on sheet X I expect to see 5, but it shows 6"?