vlookup probelm, cant see to know what wrong with the formula

hi, i have been trying to solve my vlookup formula however i couldnt find what wrong with it.

basically what im trying to do is that when the user input the timing, there will be animal output in another cell. the reason is that I will like to do dependent drop downlist.


here is the array, E7:F24:

10:30:00 Ant
11:00:00 Bear
11:30:00 Cat
12:00:00 Dolphin
12:30:00 Elephant
13:00:00 Fox
13:30:00 Gorilla
14:00:00 Horse
14:30:00 Iguana
15:00:00 Jaguar
15:30:00 Kangaroo
16:00:00 Leopard
16:30:00 Mouse
17:00:00 Scorpion
17:30:00 Octupus
18:00:00 Peguin
18:30:00 Tiger
19:00:00 Rabbit

customer will select timing in the cell V4. In cell BG3=V4 (the same timing of the user selected) while the below cell, BG4 = BG3+0.5/24 (interval of 30mins), BG5 =BG4+0.5/24 and so on. the lookup formula will be right beside BG column

this is my formula for vlookup: =VLOOKUP(BG3,Lists!E7:F24,2,TRUE)

and why is the outcome is repetetive?????

When you copy that formula down the range for the lookup table will change. Try it this way:

=VLOOKUP(BG3,Lists!$E$7:$F$24,2,TRUE)

I'm not sure how this relates to dependent drop-downs, however.

Hope this helps.

Pete

hi pete, thanks for replying.


anyway in terms of dragging down the formula is not the problem.

when I manual write the time, I could not get the correct vlookup output however when i use it a formula it will become #na.

for example:

BG3: 10:30
BG4 = BG3+0.5/24 : 11:00

the output should be Bear, however the output was #NA

however if i manual write 11:00 in BG4 i can get Bear

i check the format of the array and the cell for the input is all the same. in time format

Omgggggg, thanks you very muchhhhhhhh. Knew it i got to do with the calculation. Thanks so much glen and kev. Great help