# Lookup for reference

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 close merge delete

Sort by » oldest newest most voted

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.

more