index/index

[Bobby]

Hi
I know I need to use inde/match, but I am having trouble putting it all
together.

A1= 01/01/2006 05:00
I have a lookup grid as follows:
A3:A33 = dates 01/01/2006-01/31/2006
B2:Y2 =Hours 1-24

This is what I have so far (That wont work)
=index(B3:Y33,match(TEXT(A9,"dd/mm/yyyy"),A3:A33)*(hour(A9),B2:Y2,1))

Thanks!

 

[vezerid]

Bobby, if I understand correctly, you have a 2-D table w/ various days
in the rows and timeslots occupying columns. Also, if I understand
correctly, in your formula you mean A1 not A9 (which is part of the
data). I.e., you have one full date-time specification in A1 and you
want to search the 2-D crosstabulation for the item on the specific
time.

If this is the case, then you need the following:
=vlookup(int(A1), $A$2:$Y$33, match(mod(a1,1), $B$2:$Y$2, 0), 0)

Does this help?
Kostis Vezerides

 

[Bob Phillips]

Try

=INDEX(B3:Y33,MATCH(INT(A1),A3:A33,0),MATCH(HOUR(A1),B2:Y2,0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)