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

[artland]

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?

 

[JE McGimpsey]

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,),FALSE),FALSE)

 

[James]

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

 

[Herbert Seidenberg]

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))