Access table by column name in a cell, and row number specific ranges

[dnaiel]

Hi,

I have an excel table named incomeTable like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]name
[/TD]
[TD]spend
[/TD]
[TD]income
[/TD]
[/TR]
[TR]
[TD]mario
[/TD]
[TD]3
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]roger
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]tom
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

I'd want to access a given column and a given range of rows (but getting the column name from a specific cell).

I.e., Let us say I have in A2 the column name of interest

[TABLE="width: 500"]
<tbody>[TR]
[TD]columnNameOfIInterest
[/TD]
[/TR]
[TR]
[TD]spend
[/TD]
[/TR]
</tbody>[/TABLE]

Then, how would I access a range of rows and all the contents in that column?
=incomeTable[[1:2],[=$A2]]?
(where A2 is spend)

and it'd return
[TABLE="width: 500"]
<tbody>[TR]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mike Blackman]

Hi,

If you're looking to SUM those values you could use something like this...

=SUM(INDEX(IncomeTable,1,MATCH(A2,IncomeTable[#Headers],0)):INDEX(IncomeTable,2,MATCH(A2,IncomeTable[#Headers],0)))

 

[dnaiel]

Hi Mikey, Thanks. I am not looking to do a sum though, just looking to access a given column and a range of rows. Is that possible using the table format?