Ask Your Question
0

Lookup for reference

asked 2017-10-28 19:44:54 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I have a nutrition table on the right side of the spreadsheet. Let's say from column "P"

+---------------------+----------+------+-------+---------+
|          P          |    Q     |  R   |   S   |    T    |
+---------------------+----------+------+-------+---------+
| Name                | Calories | Fat  | Carbs | Protein |
| Brown Rice (cooked) | 112      | 0.83 | 2.32  | 23.51   |
| Chicken Breast      | 164      | 6.48 | 0     | 24.82   |
+---------------------+----------+------+-------+---------+

Now I want a table on the right left of the table, where I can write the name of the food, and the amount (in g), it looks up (by name) and fills out the rest. Something like this:

+---+----------------+-----------+----------+------+-------+---------+
|   |       A        |     B     |    C     |  D   |   E   |    F    |
+---+----------------+-----------+----------+------+-------+---------+
| 1 | Name           | Amount(g) | Calories | Fat  | Carbs | Protein |
| 2 | Chicken Breast | 70        | 114.8    | 4.54 | 0     | 17.38   |
| 3 | Chicken Breast | 100       | 164      | 6.48 | 0     | 24.82   |
+---+----------------+-----------+----------+------+-------+---------+

Where

C2 = Q2 / 100 * B2   AND   C3 = Q2 / 100 * B2

And I only have to fill out A2, B2, A3, B3

Is this possible without macro?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-10-28 21:56:15 +0100

Lupp gravatar image

VLOOKUP() is an option. As you need to look-up more than one columns by the same match a sheet will be clearer in structure and better maintainable/scalable/enhanceable if you first search for the match in a helper column and then use the common index into the lookup table for all the applying columns.

There are surely many nutrition calculators based on spreadsheets in the world. I just sketched another one.

edit flag offensive delete link more

Comments

Thanks! That looks awesome :)

MCB78 gravatar imageMCB78 ( 2017-10-28 23:59:59 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-28 19:44:54 +0100

Seen: 132 times

Last updated: Oct 28 '17