# Dynamic reference to a cell from another sheet based on text

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)?

New contributor
user929304 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
• So just to get the value of Table 1 B2 onto sheet A, what would be the syntax? 2 days ago
• @SolarMike Honestly, to get started, yes that is really what I'm looking for. Later, I intend to extend it such that given each "quantity" selected in sheet A, the preceding cell "ingredient" is taken from sheet B to know the nutritional coefficient. 2 days ago
• So why try to do the complete calculation? Just work in steps. First value, then find the correct result then do the calc. 2 days ago
• so the value of B3 on sheet 2 can be shown on sheet 1 with Sheet 2::Table1 1::B3 2 days ago
• Then use a lookup or index match to get the particular value. 2 days ago

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