You COULD use
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
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.
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.