3

I need to create a spreadsheet that should pull data from a cell according to the time of the day.

In the column "A" I have all the hours of the day, ranging from 0 to 23; in the column "F", I have series of arbitrary numbers related to a use behavior I am tracking.

I'd like to create a formula that, for example, at 4PM, show me the value of the "F" column related to the number "16". Ex: at midnight, get the "F" column value related to A1 (which is 0, because it's midnight), then F1 = X (where X is the arbitrary number at line 1).

Any ideas?

cgobbet
  • 309
  • 4
  • 12

1 Answers1

1
   =vlookup(HOUR(NOW()),$a$1:$f$24,6,0)

You were already half way there. NOW() gives you the time, Hour gives you the hour of now and vlookup returns the value in the 6th column next to your searched value.

Balinti
  • 1,524
  • 1
  • 11
  • 14
  • Balinti, thanks for the reply. I'm afraid I wasn't clear enough: – cgobbet Sep 11 '15 at 12:16
  • In the column "A" I have all the hours of the day, ranging from 0 to 23; in the column "F", I have series of arbitrary numbers related to a use behavior I am tracking. – cgobbet Sep 11 '15 at 12:22
  • I'd like to create a formula that, for example, at 4PM, show me the value of the "F" column related to the number "16". – cgobbet Sep 11 '15 at 12:23
  • Ex: at midnight, get the "F" column value related to A1 (which is 0, because it's midnight), then F1 = X (where X is the arbitrary number at line 1). – cgobbet Sep 11 '15 at 12:23
  • Yes.. I edited my answer, please edit your question. – Balinti Sep 11 '15 at 12:37
  • Thanks, Balinti: I am still lost somehow. I receive the error message: "Error Did not find value '12' in VLOOKUP evaluation." If I change the range to '$a$2:$f$25', to not include the headers of the columns, I ger the error message Error Did not find value '12' in VLOOKUP evaluation. What am I missing? Thnks in advance – cgobbet Sep 14 '15 at 15:34
  • Is A13 populated with the number 12? maybe it is shown as text? try to enter 12 manually as number. – Balinti Sep 15 '15 at 16:45
  • Cell has numbers already and still get error message. If I write the value or the cell, it works. If not, syas that the value wasn't found. I've copied here so u can see it: https://goo.gl/pbIIW8 – cgobbet Sep 16 '15 at 19:53
  • Now it is working. The problem was the numbers were not stored as numbers on your spread sheet. – Balinti Sep 16 '15 at 20:04