Vlookuping in different column according to time

[ylijohe]

I am making a program to compare stock prices. Unfortunately the free stock price server changes to position of the last prices when the market closes. When the market is open, I should vlookup column 2, but after closing time (8 pm or 20:00), Ishould vlookup column 4.

Any ideas how to solve this?
How to implement time checking to this formula:
VLOOKUP(A9;(VALUES!A$1:D$278);2;FALSE))

 

[Gregc]

=if(now()<12:00,vlookup(1),vlookup(2))
You have to change the time to excel friendly time, but I believe the general format of the formula should work.

 

[IML]

you could also use something along the lines of:
=VLOOKUP(A9,Values!A$1:D$278,2+((MOD(NOW(),1)>(20/24))*2),0)

good luck

 

[Mark W.]

=VLOOKUP(A9,VALUES!A$1:D$278,IF(MOD(NOW(),1<"20:00"+0,2,4),0)
This message was edited by Mark W. on 2002-09-17 14:17

 

[Aladin Akyurek]

On 2002-09-17 13:59, ylijohe wrote:
I am making a program to compare stock prices. Unfortunately the free stock price server changes to position of the last prices when the market closes. When the market is open, I should vlookup column 2, but after closing time (8 pm or 20:00), Ishould vlookup column 4.

Any ideas how to solve this?
How to implement time checking to this formula:
VLOOKUP(A9;(VALUES!A$1:D$278);2;FALSE))

=VLOOKUP(A9;VALUES!A$1:D$278,IF(C1>0.833333333333333,4,2),0)

where C1 houses the formula: =NOW()

You probably need to expand the condition of IF. The number 0.833333333333333 is the internal value of 20:00.

 

[Mark W.]

On 2002-09-17 13:59, ylijohe wrote:
...but after closing time (8 pm or 20:00), I should vlookup column 4.

Don't you mean at or after closing?

 

[ylijohe]

Yes. At OR after closing time...

How can I compare 0.833333333333333 and NOW(), or how to separate HOUR from NOW()?

 

[Mark W.]

Why not use...

=VLOOKUP(A9,VALUES!A$1:D$278,IF(MOD(NOW(),1)<"20:00"+0,2,4),0)
This message was edited by Mark W. on 2002-09-17 15:56

 

[IML]

On 2002-09-17 14:40, ylijohe wrote:
Yes. At OR after closing time...

How can I compare 0.833333333333333 and NOW(), or how to separate HOUR from NOW()?

Using my formulation, you could use:

=VLOOKUP(A9,values!A$1:D$278,2+(HOUR(NOW())>=20)*2,0)

This works the same as the first, but incorporates your hour idea, which, to me, seems a bit cleaner than the backing into the time with the mod route.

Several other good options are presented here.

 

[IML]

On 2002-09-17 14:45, Mark W. wrote:
Why not use...

=VLOOKUP(A9,VALUES!A$1:D$278,IF(MOD(NOW(),1<"20:00"+0,2,4),0)
This message was edited by Mark W. on 2002-09-17 14:47

ever so small typo:
=VLOOKUP(A9,values!A$1:D$278,IF(MOD(NOW(),1)<"20:00"+0,2,4),0)