Help with a f(x) formula
 

I have a data table with 5 columns and 18 rows. I have data validation
pull-down menus in separate cells, one each for selecting the column and row
headers data. In a third (adjacent) cell, I want to return the value that
corresponds to the selected values in the table. Seems simple enough, but I
am stumped.

Help with a f(x) formula
 

On Fri, 18 Nov 2005 14:12:02 -0800, Brad Lindsey
wrote:

I have a data table with 5 columns and 18 rows. I have data validation
pull-down menus in separate cells, one each for selecting the column and row
headers data. In a third (adjacent) cell, I want to return the value that
corresponds to the selected values in the table. Seems simple enough, but I
am stumped.


If your columns and rows are NAME'd by their header,
and if your cells where the user selects the names are A2 and B2,
then the formula:

=indirect(a2) indirect(B2)

will return the contents of the cell at the intersection those named ranges.
Note the <space between the two indirect functions.

If the columns and rows are NOT NAME'd, then try this formula:



=INDEX(DataTable,MATCH(B2,RowLabels,0)+1,MATCH(A2, ColLabels,0)+1)

DataTable is the entire table including the presumably empty cell in the upper
left corner.

ColLabels is the row of column headers
RowLabels is the column of row headers.

So if DataTable were $D$1:$I$19, ColLabels would be $E$1:$I$1 and RowLabels
would be $D$2:$D$19


--ron