0

I have two spreadsheets. One is called list_SheetA, which contains a list of products and their nutritional value. The columns are Proteins, Carbs, Fat, Item. All these three values are listed per 100 grams and the last one is the name of product. In turn, the second sheet calculation_SheetB contains diet calculations which contains the same columns and in addition it contains the Qunatity. The fields in the first spreadsheet are filled by me.

My questions are:

  1. Can the "HLOOKUP" lub "VLOOKUP" function be correct in this solution?

  2. How can I automatically or by using a macro or script in Python to calculate the Proteins, carbs and Fat just by entering values in the Item and Quantity in the second spreadsheet?

  3. Can I get any tips, advice on where to look for similar information or examples of solutions?

EDIT

After reading all the comments, I adjusted the formula that looks the way for exmaple in C2:

=IFERROR(VLOOKUP($I2;$Products.$A$2:$G$100;MATCH(C$1;$Products.$B$1:$G$1;0)+1;0)*$J2/100;0)

I used it for parameters as in the attached picture of sheets.

  • Hi @Michail Vizarkovyc ,, check my post, has two parts one is I call smart way to work,,, and another is exactly what you are looking for,,, nothing manual,, all driven by formula. Capable to handle even large data set also ☺ – Rajesh S Jun 21 at 13:46
  • Hi @Michail Vizarkovyc ,, but calculation sheet getting ZEROsss ,,, and NO value in Column I,,, in old calculation sheet you have products/fruits in duplicate and source sheet List has unique,,, how U are duplication products/fruits and related qty !!! – Rajesh S Jun 22 at 4:47
1

You COULD use VLOOKUP() (HLOOKUP() does not apply here as your data is up/down, not left/right). But to do so, you would have to move your lookup value's column as VLOOKUP() only looks to the right of the lookup values (Item) and as you describe the setup, the lookup values (Item) are currently on the far right.

My guess is you'd be happy enough doing that. If not though, then you must use a different approach. The new XLOOKUP() function is ideal, but if you do not have it in your Excel version, the INDEX/MATCH approach has been the most popular method on the internet for a very, very long time. Easy to find it explained on lots of websites and in lots of videos on YouTube.

To calculate each of the three fields you are asking about, simply do a lookup of the Item using VLOOKUP() (if you moved the Item column to be column A) or XLOOKUP(), as desired. In the same formula, simply multiply each result by the total grams over to the right of these fields (it has a typo: it should be "Quantity", by the way) and divide by the 100 grams each input value is based upon. So the formula, generalized, would be"

=VLOOKUP result * Quantity / 100

Easy to do, I promise. When you write the VLOOKUP() for the first one, just copy it over to the second and third, then edit as needed. To make that easier, notice that the lookup value will always be in column D, only changing in the row the formula is in so add a $ to the column but not the row: $D2 for example. A benefit is that it will copy to the right staying the same so it won't need editing. The column range ("lookup table" in VLOOKUP-speech) for the second parameters DOES need to change when you copy the first formula to the right but only the second part of the range, not the first column, so use a $ on the first part of the range and don't use a $ on the second: $A2:B3001 for example. Then copying to the right two cells will see the second half of eahc lookup range change for you. Be sure to use "FALSE" or "0" for the fourth parameter or you will be back here reporting it doesn't always work right. So the editing I referred to can be cut down to changing the lookup column from 2 to 3 and 4 for the second and third lookups. Even that can be "automated" as you could use the expression COLUMN()+1 which will give you the "2" you need in column A and so on to the right. Then you just copy rightward AND down the SINGLE formula you had to write.

That's it.

Now, in the preceding paragraph I told you a way or two to "be slick" in how you make your formula so you can just copy it to the right and down after writing one formula, instead of having to write three. Nice to think of doing that since each time you, the human, does writing rather than letting Excel do it, you risk error. But there is one more slickness that you might easily like to add, though it seems very unlikely you would ever need it for this. Good practice if you like, though, for other times when you might.

The basic idea is to pick the VLOOKUP() return column (third parameter, the one that says "return this column's data") by using MATCH() to match the header of your output page column to the header of those data columns on the data sheet. It will return the ordinal number of the match in the range you have it examine but as a cardinal number you can use in VLOOKUP(). So it it looks for "Carbs" and finds it matches the 1st item in its lookup list on the data sheet, you can give that number for the column VLOOKUP() should return a value from... well, you have to consider how it might be offset compared to the lookup (which means adding 1 to its result in this case), BUT if you get slicker yet, you specify a lookup range for it that starts with the same column the lookup table does, so it matches exactly and does not need an offset. So you could use the MATCH() function below to do this:

=MATCH( A1, 'list_SheetA'!A1:D1, 0 )

(This is LIKE performing an HLOOKUP() in a way and maybe you have been introduced to it before in an application of it that did use HLOOKUP() with it. Some uses do, so... But still, just MATCH() is needed here because you simply need a number, not a return value.)

And if you like, you can apply the earlier kinds of "slickness" to this, but I'm about slicked out at the moment so I'll leave that to you.

DO NOTE: if you do this matching thing, you are protected against adding or deleting columns in your data sheet, at least between the existing columns (and can cover "after them" by simply extending the range to, say, E1 so even a column added between D and E would still let it work) since the header name is still being looked up after column havoc occurs, and will always return whatever column it is really in. HOWEVER, those column names HAVE TO ALWAYS ACTUALLY MATCH, eh? So you would be well-advised to not type one set of them in, but rather to use formulas to show them. Then they will always match, no matter what. And work with column adds/deletions since Excel will change their column reference to stay right. So, on the output sheet, use "=B1", "=C1", and "=D1"... Better though, Create Named Ranges named the three names, then enter "=Proteins" and so on for both sets of headers. This adds value in several ways, but obviously, while 3 columns are cool, 23 columns needing 23 Named Ranges might start to... aggravate you. You CAN use Autonaming, but that becomes complicaed if your headers take on complicated names (spaces, Alt-Enter's, that kind of thing).

Alrighty, unless I grotesquely misunderstood, you only really want stuff ending a far way above here, so I shall end.

| improve this answer | |
0

If you are using a version of Excel that doesn't support XLOOKUP then you can use this formula:

=VLOOKUP($G2,$A$2:$D$5,2,FALSE)*$H2/100

Modified for your sheet names and table locations.

To use VLOOKUP, move your entered "Item" to the left of the values you are looking up on Sheet1.

enter image description here

In your formula, the A2:D5 part will be the range from your Sheet1.

The formula works as we can quickly check that since Plum contains 3 grams of protein per 100g, then 160g contains 3 x 1.6 = 4.8 grams of protein.

If you are using the latest version of Excel and you have access to the XLOOKUP formula, you can achieve the same result by putting this formula in just the first column for each row:

=XLOOKUP($G2,$A$2:$A$5,$B$2:$D$5)*$H2/100

XLOOKUP is nice because it can return more than one column of data, so actually it just looks for the row that contains the Item you're looking for, then returns all the nutrient information for that row.

enter image description here

| improve this answer | |
  • Instead of write formula for each column (I, J & K) you may enter this Array formula in I2, =VLOOKUP($G2,$A$2:$D$5,{2,3,4},FALSE)*$H2/100, and drang till K2, then press F2 and finish with Ctrl+Shift+Enter and fill across (I3:k4). But the shown method is lengthy , since one Table can do this . – Rajesh S Jun 21 at 10:17
0

You may try this method, since is a smart way to calculate nutrients for each fruit.

enter image description here

How it works:

  • Formula in cell N41 in Sheet 2:

    =SUMPRODUCT(((Sheet1!$M$33:$M$36=$M41)*(Sheet1!$N$32:$P$32=N$40)*Sheet1!$N$33:$P$36))*N$39/100
    

N.B.

  • Cell N39, O39 & P39 has variable weight, and are editable also.

  • You may adjust cell references in the formula as needed.


:Edited:

I'm posting this method since OP wants to calculate nutrients for variable quantities for each fruit, where each fruit appears as duplicate data.

enter image description here

How it works

  • Column F, G & H are Helper Columns.

  • Enter fruit's name in column F as shown.

  • Repeat value in column G, and variable quantities in column H.

  • Formula in Sheet 2, cell J2:

    =IFERROR(IF(COUNTIF(J$1:J1,J1)<IFERROR(VLOOKUP(J1,$F$2:$G$5,2,FALSE),0),J1,INDEX($F$2:$F$5,MATCH(0,INDEX(COUNTIF(J1:J$1,$F$2:$F$5&""),0,0),0))&""),"")
    
  • Enter this formula in Sheet 2, cell K2:

    =IF(ROW()>COUNTA(H:H)*12,"",INDEX(H:H,MOD(ROW()-2,COUNTA(H:H))+1))
    
  • Final formula in Sheet 2, cell L2:

    =SUMPRODUCT(((Sheet1!$A$2:$A$5=$J2)*(Sheet1!$B$1:$D$1=L$1)*Sheet1!$B$2:$D$5))*$K2/100
    

N.B.

  • Fill the formula across the range.
  • Adjust cell references in the formula as needed.
| improve this answer | |
  • This doesn't calculate the nutrients correctly. OP wanted to calculate protein, carbs and fat for 160g of Plum, for 120g of Plum and for 80g of Plum. – FlexYourData Jun 21 at 10:21
  • Row 43 ,, N43:P43 calculates Plum out of 160, 120 & 80. Data orientation is as TABLE 1 the Source Table. – Rajesh S Jun 21 at 10:26
  • The stated purpose is to enter a single quantity of an item in grams, then use the values per 100g in sheet1 to calculate the amount of each macronutrient for that quantity. So, "the quantity of protein in 160g of Plum, the quantity of Carbs in 160g of Plum, the quantity of Fat in 160g of Plum". Then, separately, the same for 120g of Plum. And again, separately for 80g of Plum. – FlexYourData Jun 21 at 10:32
  • @Owen, TABLE 1 is based on per 100 gms and Table 2 calculate each out of 160, 120 & 80. Like in N41 is =N33*160/100 gets Protein in Apple out of 160 and so others. Actually I've tried OP to suggest, use method show by me but not the way OP wanna to calculate ,,, it need to repeat exercise as per OP example 4 times. What happens if has 100 items !! – Rajesh S Jun 21 at 10:38
  • According to OPs data, Plum contains more fat than protein per 100g of Plum. But your results show 3.2 for fat and 4.8 for protein. How can this be? – FlexYourData Jun 21 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.