1

Here is my problem:

I have a table starting in A1 with D

D C B A
1 2 3 4
5 6 7 8

I want a function that returns me the column of data of whatever letter give it.

ie input:=function("C")

output:

2
6

input: =function("A")

output:

4
8

I can't just query because the column is changing.

Hlookup? but how do you get the whole column as a return?

5
  • say if i want column with heading C statement would be =query(A1:D3,"select B where B='C') now I want column with heading A, =query(A1:D3,"select D where D='A'). The letter behind select needs some type of matching with column letters. I'm not sure this is the best way to go about it.
    – jason
    Aug 29, 2014 at 14:35
  • yeah you're right, so how do I make it dynamic?
    – jason
    Aug 29, 2014 at 14:38
  • I'm pasting this formula into many spreadsheets. I wouldn't waste my time trying to get a "cute" formula for 10 characters.
    – jason
    Aug 29, 2014 at 14:42
  • ha... ok... anybody else?
    – jason
    Aug 29, 2014 at 14:46
  • @pnuts, the OP would like to retrieve the column that has a certain string in the header, whether it is "C", "Foo" or "Strawberries".
    – AdamL
    Aug 30, 2014 at 21:29

3 Answers 3

2

If I'm understanding correctly:

=FILTER(A2:D3,A1:D1=A5)

1
  • Thanks Adam. I find your answers are always correct and the best possible solution.
    – jason
    Aug 31, 2014 at 0:52
0

not pretty but it works.

=indirect(SUBSTITUTE(ADDRESS(1,COLUMN(hlookup(A5,A1:D3,2,false)),4),"1","")&"2:"&SUBSTITUTE(ADDRESS(1,COLUMN(hlookup(A5,A1:D3,2,false)),4),"1","")&"3")

A5 being the place where you enter the letter

0

With a trigger cell in A5 and in say A6:

="select "&A5

=query($A$1:$D$3,$A$6,0)

might suit.

This might be placed almost anywhere and produce the same results, with such results controlled by the value (letter) chosen in A5. Perhaps parameterised rather than dynamic.

2
  • Your answer doesn't work. If I put in "C" I get 3 7, not 2 6 like I want because "C" is actually asking for column B. So there needs to be some kind of a lookup function.
    – jason
    Aug 29, 2014 at 22:58
  • This is why in the example I on purpose flipped the letter heading to D C B A.
    – jason
    Aug 29, 2014 at 23:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.