Output a value in a cell, based on 2 values from 2 columns (Not in same row)

Hi Collette,

Apologies for the delay. Other household issues intervened.

The critical issue regarding copying data from the source table (here renamed Data to provide a distinct name and shorten the formulas) is that it requires more than two search values to locate the cell from which to retrieve the data.

I used MATCH and INDEX to find each value requested (on a more simplified table), then return that value to the cell containing the formula in the receiving table (here named Selected). The image of this table is a bit startling, as I've not filtered out the error triangles. These all report the same error—'Can't find what was asked for.' as the only data on the Data table is the data supplied for Oats and Wholegrain Rice.

Data table:

There are no formulas on this table. All data is entered directly from the keyboard, or copied from a different source and pasted here.

I've removed the 'other than 100g value columns as these would require revised labelling for each column to include the sample size and the item (eg. Oats 100g) to satisfy MATCH.

I also shortened the Wholegrain Rice item name to shorten formulas.

The receiving table (Selected)

The table contains one formula, entered in cell B2, then filled into all cells from B2 to I11

B2: INDEX(Data::$A$1:$E$10,MATCH(B$1,Data::$A,0),MATCH($A2,Data::$1:$1,0))

The first MATCH searches column A of Data for the value (Oats) in row 1 of 'this column', and returns the number of the row in which it is found (or an error if it is not found)

The second match searches row 1 of Data for the vlue in 'this row' of column A of 'this table, and returns the column number in which it was found (or a 'can't find' error).

The two values are handed to INDEX, which uses then to find the cell from which the data is to be collected to the cell containing this copy of the formula.

Regards,

Barry

Hi Collette,

You have shown images of Table 1 on Sheet 5 and Table 1 on Sheet 2, but speak of "Sheet 2" and "Sheet 3."

Which Table is the Source table?

Which Table is the Receiving table?

At first glance, this looks like a LOOKUP situation. Can you provide a more detailed description of what you want to happen?

Regards,

Barry