You possibly can add up values discovered by two or extra VLOOKUPs in the identical method assuming they’re all numbers and there are not any errors. Nevertheless, for search capabilities similar to VLOOKUP to work as meant, you want to have the identical names for use in each search formulation and the sheets being searched by means of besides the capitalizations.
This suggests a big drawback in your present setup which is the respective names for many substances within the recipe sheets not being the identical as these within the ordering information sheet. For instance, Maldon salt known as “Salt, Maldon” within the ordering information sheet however “Maldon Salt” within the Pizza Dough recipe sheet. Whereas each of those names would possibly seem as practically the identical to a human, they’re fairly completely different to Numbers.
To repair this, for instance, it is best to use the identical title for Maldon salt in all places together with the ordering information sheet. If you want to have a unique title within the ordering information sheet than within the recipe sheets, then you might add a (hidden) column earlier than the “Description” columns within the ordering information sheet, have the cells in these columns carry the identical ingredient names as within the recipe sheets and use these to seek for Maldon salt within the recipe sheets.
The opposite potential drawback for you possibly can be a too excessive variety of recipes which might result in too lengthy search formulation. You want as many VLOOKUPs because the variety of recipe sheets in every “Quantity to Order” cell method within the ordering information sheet to get the right whole for an ingredient. I feel a method with ten to fifteen VLOOKUPs may be manageable and keep throughout the method size restrict Numbers impose (undecided what the restrict in Numbers is however I reckon it isn’t as excessive because the 16k characters restrict for a method in Excel).
Observe that you possibly can keep away from manually creating a unique search method for every ingredient within the ordering information sheet in case you use absolutely the referencing correctly. After creating a correct method for under one of many substances, you possibly can copy & paste it to all different “Quantity to Order” cells within the ordering information sheet to get the entire for every ingredient.
Should you truly run into the issue of too-long formulation within the ordering information sheet, you might take into account having multiple recipe in a single sheet and utilizing the SUMIF or the SUMIFS capabilities as a substitute of VLOOKUP.
Good luck!