HI KC,
What you are describing is usually done with a 'lookup' formula in F77 (and other sells in that column) getting the value from column B of the same row, then looking up that value in a separate, fixed values, table and returning the value from the same row of that lookup table.
This won't work in your demo table as constructed for a couple of reasons:
The contented of the two cells in column B of the example contain different values—a function searching for "Mince" (B77) will not find "Minced Garlic" (B70), and will return an error.
That function would be searching the same column from which it gets is search value. That action leads to a circular reference (or 'self reference)—a reference to a cell whose value depends on the result of the formula that is getting a value to be used to determine the value in the cell that it is getting the value from…
Given that situation, Numbers will show an error triangle, marking a 'self-regerence error.
Here's an example of a working pair of tables doing what you want. the example is done using people names and data, but the process is the same with your data.
The Data table contains no formulas. All data on this table is entered via the keyboard (or by copy and paste).
Table 1 contains the lookup formula shown below the tables, entered in cell B2, then filled down to the end of that column and filled right to column E.
The formula has two parts:
The core part of the formula, which does the lookup and returns the data to its cell, is shown in bold. The IF statement it is wrapped in suppresses calculation until there is data entered in 'this row' of column A.
IF(A2="","",VLOOKUP($A2,Data::$A:$E,COLUMN(),FALSE))
IF A2 is empty, A2="" returns TRUE, and IF puts a null string ( "" ) in the formula's cell, as in all cells of rows 3, 5, 6, 7, 8, 9 and 10.
IF A2 contains any value (other than a null string), A2="" returns FALSE, and IF calls the VLOOKUP formula.
VLOOKUP gets the search value from 'this row' of column A, then looks for than value in the first column of the lookup table.
If it is found, COLUMN() returns the number of the column containing this copy of the formula (column 2) and returns the value in the same row of that column as the search value was found.
As the formula is filled to the right, the references to column $A of 'this table' and columns $A:$E of the Data table are preserved by the $ operator to reference the same cell or range of cells. The number returned by COLUMN changes to match the position of each column in 'this table', and determines which column of Data will be the source of the data to be transferred to 'this table'.
Regards,
Barry
