How do I reference one cell in a table by using both the row and .
 

If I have a table with column headings and row headings that are different
and I want to find out the information for a particular cell based on what is
in those headings, how would I do that. i.e. if I had columns marked 1-10
and rows labled 2005 - 2010 how would I find the information of the cell that
matched column 6 and row 2008?

One way:

Assuming the column/row you want to look up is in K1/K2, respectively,

=VLOOKUP(K2,A1:J6,MATCH(K1,A1:J1,FALSE),FALSE)


or, if you named your table "table":

=VLOOKUP(K2,table,MATCH(K1,OFFSET(table,,,1,),FALS E),FALSE)



In article ,
"artland" wrote:

If I have a table with column headings and row headings that are different
and I want to find out the information for a particular cell based on what is
in those headings, how would I do that. i.e. if I had columns marked 1-10
and rows labled 2005 - 2010 how would I find the information of the cell that
matched column 6 and row 2008?

You can use the index and match functions for this. If your data is in
A1:K7, and the column headings are in Row 1 and the row headings are in
column A use the following. You can then enter the column you want in A10
and the row in A11

=INDEX($A$1:$K$7,MATCH($A$11,$A:$A,0),MATCH($A$10, $1:$1,0))

James


"artland" wrote in message
...
If I have a table with column headings and row headings that are different
and I want to find out the information for a particular cell based on what
is
in those headings, how would I do that. i.e. if I had columns marked 1-10
and rows labled 2005 - 2010 how would I find the information of the cell
that
matched column 6 and row 2008?

Another way:
Name the row heading Year
Name the column heading Amount
Name the table Array
=SumProduct((Year=K1)*(Amount=K2)*Array)
If the table contains text, use
=Index(Array,Match(K1,Year,0),Match(K2,Amount,0))