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

[dnaiel]

Hi,

I have an excel table named incomeTable like this:

name | spend | income
mario | 3 | 10
roger | 4 | 5
tom | 5 | 4

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

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

columnNameOfInterest
spend

Then, how'd I access a range of rows and all the contents in that column?
=incomeTable[[1:2],[=$A2]]

and it'd return
3
4

[Bernie Deitrick]

Try

=HLOOKUP($A$2,incomeTable[#All],2,FALSE)
=HLOOKUP($A$2,incomeTable[#All],3,FALSE)

Note that instead of 1 and 2, use 2 and 3 to account for the headings. If you want to make the formula more 'copyable' use

=HLOOKUP($A$2,incomeTable[#All],ROW(A2),FALSE)

and the A2 will increment as you copy the formula down.

If you want to transpose the values into a row, then use

=HLOOKUP($A$2,incomeTable[#All],COLUMN(B1),FALSE)

and copy across.

[dnaiel]

Hi Bernie, this sounds right, but when I do the HLOOKUP it only returns the first value. I.e., 3 in the example. How can I make it return all the row values in the spend column, i.e., 3,4 and 5.

I tried, =HLOOKUP($A$2,incomeTable[#All],ROW(A2),FALSE), but it gave me a reference error

Or also select a range of the number of rows to return?

Thanks in advance!

[dnaiel]

never mind, my bad, all works great, thanks so much!