Dynamic reference to a cell from another sheet based on text

Question

I am trying to learn how to refer "dynamically" to a cell from another sheet given a textual condition.

I have the following scenario:

Example

There are two sheets labelled "A" and "B".

In sheet "B" I have a table of 12 rows and 4 columns. Each row there is labelled with a nutritional label, such as "Oats", followed by columns giving corresponding calories, Carbs, etc (see image below).

In sheet "A", I refer to each food with a subsequent column of quantity as shown below.

---

Objective

From sheet "A", I am trying to calculate the calroies of a meal, so for each ingredient I want to multiply its quantity with its equivalent calories per 100 grams as provided in sheet "B".

Requirement for the formula: the ability to refer to a cell of another sheet based on a textual condition from the current cell. For example, I want to be able to say the quantity at hand corresponds to "Oats" as the column preceding "Quantity 1" is "Oats". So intuitively, I have 50 grams of Oats (sheet A) that I want to multiply by the calories of Oats (taken from sheet B per 100g). So I'm trying to express the formula such that it automatically takes the preceding cell to quantity and treats its text, here "Oats", as the cell reference to sheet B.

This is the first time I am trying to do dynamic referencing. My attempt (not working) has so far been along the lines of using INDIRECT function to refer to corresponding cell of sheet B given the text "Oats". So I have tried:

A::Table 1::E2 * B::Table 1::Oats&"::B2"

But it gives an error. Even if it worked, this would still not be what I really wanted to achieve, because my ideal intention is the ability to the preceding cell of B2 from sheet B without having to explicitly specify "Oats".

---

Question:

1. Where am I going wrong with my syntax in the formula in referring to another sheet (B) and taking its cell corresponding to 2nd column of the row "Oats"? Should I somehow be using the functions INDIRECT and ADDRESS together (such as in this example)?

2. Is there a way to generalize this, where we no longer have to specify even the food, but we'd just select the quantity and the food would automatically be taken as the cell preceding it (row-wise)?

Answer 1

You could use xlookup function. If table containing calories is named Nutritional_value and have structure you provided in screenshot this formula works: xlookup takes value from column Ingridient 1 in current table, looks for same value in first column (A) in Nutritional_value table and returns corresponding value from Calories column; then it's multiplied with value in column Quantity 1 and divided with 100 (to get correct calories):

XLOOKUP(Ingridient 1 'Monday - breakfast';Nutritional_value::A;Calories)×Quantity 1 'Monday - breakfast'÷100

However, this approach is not scalable very well (you must do the same for other ingridients in a meal) and if you want make any analysis it's poor design. IMHO better would be to have one row per ingredient (data table) and analysis should be made in Pivot Table