Help with a f(x) formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brad Lindsey - 18 Nov 2005 22:12 GMT

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.

Reply to this Message

Ron Rosenfeld - 19 Nov 2005 01:05 GMT

>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

Reply to this Message