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?

  • 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 '14 at 14:35
  • yeah you're right, so how do I make it dynamic? – jason Aug 29 '14 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 '14 at 14:42
  • ha... ok... anybody else? – jason Aug 29 '14 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 '14 at 21:29
2

If I'm understanding correctly:

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

  • Thanks Adam. I find your answers are always correct and the best possible solution. – jason Aug 31 '14 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.

  • 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 '14 at 22:58
  • This is why in the example I on purpose flipped the letter heading to D C B A. – jason Aug 29 '14 at 23:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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