Lookup help

[Sandy]

Hello
I have a grid with hourly data. For each day of the month. Hours ending
1-24 in cells C2:Z2 and date in B2:B33. I am trying to convert the tabular
data to columnar data. In B41:B785 I have the date/time dd/mm/yyyy hh:mm. I
can use INDEX(My_Range,DAY($B41),HOUR($B41)) for hours 1-23 but it doesnt
work in 24. I could change my table to 0-23 and it would work but I would
rather not do that.
Any ideas?
Thanks!

 

[Sandy]

Hello
I have a grid with hourly data. For each day of the month. Hours ending
1-24 in cells C2:Z2 and date in B2:B33. I am trying to convert the tabular
data to columnar data. In B41:B785 I have the date/time dd/mm/yyyy hh:mm. I
can use INDEX(My_Range,DAY($B41),HOUR($B41)) for hours 1-23 but it doesnt
work in 24. I could change my table to 0-23 and it would work but I would
rather not do that.
Any ideas?
Thanks!

 

[T. Valko]

On second though changing 0-23 wont work

It should:

=INDEX(My_Range,DAY($B41),MATCH(HOUR($B41),$C$2:$Z$2,0))

Where C2:Z2 = 0 to 23

 

[Sandy]

Thanks!

It should:

=INDEX(My_Range,DAY($B41),MATCH(HOUR($B41),$C$2:$Z$2,0))

Where C2:Z2 = 0 to 23

 

[Sandy]

Hey Biff
One little problem with the hour 24 of the last day of the month, it shows
as day 1. Is there a work around??
Thanks for your help

 

[T. Valko]

Hmmm...

Well, I'd need to be able see what you're trying to do to figure it out. If
you can/want to send me a small sample file that demonstrates exactly what
you're doing I should be able to figure it out.

If you want to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Or, you can upload a small sample file to a free file host then post a link
to that file. Here are a couple of free file hosts:

http://translate.google.com/transla...m=1&ct=result&prev=/search?q=cjoint&hl=en&lr=

http://www.freefilehosting.net

The first link is one I use frequently. It's a French site translated to
English. The uploaded file is removed after a few weeks and the link will
"break" at that time.