There are actually a few different methods that can be used for this
kind of problem, but the solution described below is easy to implement
and can be extended for more food items and is quick to use when
entering your diary items.
Let us assume that your journal will be on one sheet (say Sheet1) and
the list of information from your trainer is on a second sheet (say
Sheet2) (the food list, portion size, and other information).
In an empty spreadsheet, I selected Sheet2 (at the bottom of the
window), and entered the following information (from your example) in
cells A1 through F4). This list can be extended for as many rows as
the types of food from your trainer.
Food Portion Protein Carbs Fat Calories
Salmon 3 oz 23 0 9 173
Green beans 1/2 cup 1 2 0 12
Wheat bread 1 slice 2 11 1 61
I suggest you define a name for the cells that contain this
information (in case you add / remove rows or move the list). To
define a name, select the region (click / drag over the cells) and
then use the menu
Insert -> Name -> Define
and then enter a name like Food to refer to that region in the
formulas below as Food. You can include or exclude the header row if
you like. I also suggest making the region a little larger than you
currently have food items & insert rows somewhere in the middle of the
region when adding new food items.
NOTE: From my testing, the case (upper or lower) of the food items can
vary. So a name like salmon, SALMON, and Salmon will all "match"
Now in Sheet1 (be sure to select Sheet1 at the bottom of the window),
go ahead and enter the same column headers. [In the sample sheet I
prepared, I did this in A1 through F1]
In cell B2, enter the following formula:
Let me explain what this formula will do.
The VLOOKUP function is used to look up values in a region of cells.
The first value ($A2) refers to the cell in column A in absolute terms
and row 2 in relative terms. That is the value that will be matched in
The second value (Food) refers to the region of data to look up. The
first column has the values that are matched to determine the row that
data will be extracted. Be sure to use the same name you entered when
you named the region on the second sheet.
The third value (2) refers to the column to extract the data. In this
case, the second column (referring to the portion size) will be
The fourth value (FALSE) is used to require an exact match. In this
way, the information can be entered in the region in any order (if
TRUE - the entries MUST be in alphabetical order).
There is additional information in the on line help for Microsoft
Excel - enter VLOOKUP as a search phrase to find that information (as
well as similar functions).
At this point, I suggest you select the cells B2 through F2 (click
drag) and fill right using the menu
Edit -> Fill -> Right
All those values should show #N/A at this point (since you don't have
a value in cell A2). Go ahead and change A2 to salmon (or another food
item) and the entire row should show the portion size for that food
Now, let's modify the cells in C2 through F2 to fetch the proper
values. As identified above, the third value is the column to fetch so
change C2 through F2 as follows
If you have a proper food item in cell A2, the values in row 2 should
display like the example you provided.
Now you can select B2 through F50 (or as many rows you want) and use the menu
Edit -> Fill -> Down
and #N/A should appear on all rows where there is no food item entered.
[let me know if these must be blank - I can suggest a slightly
different formula to do that as well]
As you enter each food item in column A, the values should be fetched
properly from the "Food" region on Sheet2. If the food item is
misspelled, the #N/A will still be shown.
Also, if any part of the answer is unclear, you have any difficulty
with the formulas, or you need additional enhancements to the solution
(as mentioned above or otherwise), please make a clarification
request. I would be glad to help further.
Good luck with your training.