Allison W. answered • 03/19/22
Excel Automation & Analytics Fanatic
Hi Wade,
Either VLOOKUP or the newer XLOOKUP in Excel should work nicely for this. I personally think XLOOKUP is a bit more intuitive to use.
For your first mix, you listed both Roundup and Cinch. One way to do this, is to insert an XLOOKUP formula next to each cell that contains your ingredients. So in the cell right next to where it says "Roundup", I've written out an XLOOKUP formula, assuming that the text "Roundup" is in cell A2 on the Mixes tab.
=XLOOKUP(A2,Master!$A$2:$A$4,Master!$B$2:$B$4)
Here are the arguments that the formula can take, separated by commas (the brackets denote optional arguments that can be skipped):
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_model],[search_model])
The first argument needed is the lookup_value - i.e., what is the thing you need to go find information for? In this case, what you want to lookup is the text "Roundup", which is contained in cell A2. So you can just put in A2 for the lookup_value.
The next argument needed is the lookup_array - where is the array or range of cells on your "Master" tab where you'd find the text "Roundup"? Well, that's going to be on the Master tab, and when I set my own version of your data, I listed the ingredient names in range A2:A4 on the Master tab. I've "locked" these cell references (i.e., put dollar signs in front of each number and letter) so that you'll be able to drag this formula down and these cell references won't change (because they will, otherwise - that's Excel's default setup). In other words, we only want to write the formula once, and then be able to copy it as many places as we want.
After we've identified the lookup_array, we want to identify the return_array. We're looking for the price associated with our Roundup ingredient - where is the range of cells where we can find price information? When I set up a copy of your data in my own worksheet, I listed the corresponding ingredient prices in range B2:B4 on the Master tab - and again, I've locked the references.
The rest of the arguments, shown above in [brackets], are optional, and so you can stop here if you wish. I do find the optional if_not_found argument helpful, however - let's say your formula can't find the text "Roundup" in the lookup_array. If you don't put anything for the if_not_found argument, your formula cell will display an #N/A error. Would you rather have it display #N/A, or would you rather it display some other text? For example, if you'd like your formula to show the text "Ingredient not found" if it can't find the ingredient to the left of the formula, you can do it by adding one more comma, and then typing (including the quotation marks): "Ingredient not found" - and then close your parantheses.
Notice how we have NOT locked the lookup_value of A2. This is because we want the reference to change from A2 to B2 when we copy the formula down to the next cell, so that in cell B3, the formula will be looking up the price for Cinch, rather than Roundup. (This is hard to picture, since I couldn't provide screenshots, but feel free to reach out to me and we can discuss further.)
Once you've written the formula in cell B2 on our "Mixes" tab, and copied the formula down to B3, you will have a formula that can look up the prices for your ingredients.
To get the grand total for Mix #1, we will need a different formula. So far, all we've done is retrieve the individual ingredient prices, but we want to know the total cost of Mix #1. A simple SUM formula will suffice here, since we've already retrieved the individual ingredient prices, to show the total cost for Mix 1: =SUM(B2:B3)
Whether you want to set up your SUM with locked references or not may depend on exactly how this sheet will be used.
I hope this helps! I would've liked to have included screenshots, but feel free to message me if this is unclear.
