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

[zakizelani]

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:

[TABLE="width: 135"]
<tbody>[TR]
[TD="align: right"]10:30:00[/TD]
[TD] Ant[/TD]
[/TR]
[TR]
[TD="align: right"]11:00:00[/TD]
[TD] Bear[/TD]
[/TR]
[TR]
[TD="align: right"]11:30:00[/TD]
[TD] Cat[/TD]
[/TR]
[TR]
[TD="align: right"]12:00:00[/TD]
[TD] Dolphin[/TD]
[/TR]
[TR]
[TD] 12:30:00[/TD]
[TD]Elephant[/TD]
[/TR]
[TR]
[TD="align: right"]13:00:00[/TD]
[TD] Fox[/TD]
[/TR]
[TR]
[TD="align: right"]13:30:00[/TD]
[TD] Gorilla[/TD]
[/TR]
[TR]
[TD="align: right"]14:00:00[/TD]
[TD] Horse[/TD]
[/TR]
[TR]
[TD="align: right"]14:30:00[/TD]
[TD] Iguana[/TD]
[/TR]
[TR]
[TD="align: right"]15:00:00[/TD]
[TD] Jaguar[/TD]
[/TR]
[TR]
[TD="align: right"]15:30:00[/TD]
[TD] Kangaroo[/TD]
[/TR]
[TR]
[TD="align: right"]16:00:00[/TD]
[TD] Leopard[/TD]
[/TR]
[TR]
[TD="align: right"]16:30:00[/TD]
[TD] Mouse[/TD]
[/TR]
[TR]
[TD="align: right"]17:00:00[/TD]
[TD] Scorpion[/TD]
[/TR]
[TR]
[TD="align: right"]17:30:00[/TD]
[TD] Octupus[/TD]
[/TR]
[TR]
[TD="align: right"]18:00:00[/TD]
[TD] Peguin[/TD]
[/TR]
[TR]
[TD="align: right"]18:30:00[/TD]
[TD] Tiger[/TD]
[/TR]
[TR]
[TD="align: right"]19:00:00[/TD]
[TD] Rabbit[/TD]
[/TR]
</tbody>[/TABLE]

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?????

 

[AlanY]

a very weird problem, I've reproduced your data here with both vlookup()s produced wrong results for 11:30:00 etc

Excel 2013/2016
	A	B	C	D	E	F
7	10:30:00	Ant	Ant		10:30:00	Ant
8	11:00:00	Bear	Bear		11:00:00	Bear
9	11:30:00	#N/A	Bear		11:30:00	Cat
10	12:00:00	#N/A	Cat		12:00:00	Dolphin
11	12:30:00	#N/A	Dolphin		12:30:00	Elephant
12	13:00:00	Fox	Fox		13:00:00	Fox
13	13:30:00	Gorilla	Gorilla		13:30:00	Gorilla
14	14:00:00	Horse	Horse		14:00:00	Horse
15	14:30:00	#N/A	Iguana		14:30:00	Iguana
16	15:00:00	#N/A	Jaguar		15:00:00	Jaguar
17	15:30:00	#N/A	Kangaroo		15:30:00	Kangaroo
18	16:00:00	#N/A	Leopard		16:00:00	Leopard
19	16:30:00	#N/A	Mouse		16:30:00	Mouse
20	17:00:00	#N/A	Scorpion		17:00:00	Scorpion
21	17:30:00	#N/A	Octupus		17:30:00	Octupus
22	18:00:00	#N/A	Peguin		18:00:00	Peguin
23	18:30:00	#N/A	Tiger		18:30:00	Tiger
24	19:00:00	#N/A	Rabbit		19:00:00	Rabbit
Sheet1

Cell Formulas
Range	Formula
A9	=A8+0.5/24
B9	=VLOOKUP(A9,$E$7:$F$24,2,0)
C9	=VLOOKUP(A9,$E$7:$F$24,2,1)

but if I copy and paste values only from E7:E24 to A7:A24 it works for both vlookup()s, looks like it's a rounding off problems

Excel 2013/2016
	A	B	C	D	E	F
7	10:30:00	Ant	Ant		10:30:00	Ant
8	11:00:00	Bear	Bear		11:00:00	Bear
9	11:30:00	Cat	Cat		11:30:00	Cat
10	12:00:00	Dolphin	Dolphin		12:00:00	Dolphin
11	12:30:00	Elephant	Elephant		12:30:00	Elephant
12	13:00:00	Fox	Fox		13:00:00	Fox
13	13:30:00	Gorilla	Gorilla		13:30:00	Gorilla
14	14:00:00	Horse	Horse		14:00:00	Horse
15	14:30:00	Iguana	Iguana		14:30:00	Iguana
16	15:00:00	Jaguar	Jaguar		15:00:00	Jaguar
17	15:30:00	Kangaroo	Kangaroo		15:30:00	Kangaroo
18	16:00:00	Leopard	Leopard		16:00:00	Leopard
19	16:30:00	Mouse	Mouse		16:30:00	Mouse
20	17:00:00	Scorpion	Scorpion		17:00:00	Scorpion
21	17:30:00	Octupus	Octupus		17:30:00	Octupus
22	18:00:00	Peguin	Peguin		18:00:00	Peguin
23	18:30:00	Tiger	Tiger		18:30:00	Tiger
24	19:00:00	Rabbit	Rabbit		19:00:00	Rabbit
Sheet1

Cell Formulas
Range	Formula
B9	=VLOOKUP(A9,$E$7:$F$24,2,0)
C9	=VLOOKUP(A9,$E$7:$F$24,2,1)

 

[zakizelani]

hi alan Y , thanks for replying.

yes this is result i have been getting. (first image)

however can you explain the second image? (solution?)

 

[zakizelani]

Hi alan y, i have the solution. Yes it is the rounding off is causing the problem

 

[AlanY]

would like to know how do you solve it.

 

[zakizelani]

here you go sir,

=VLOOKUP(ROUND(1440*BG3,0)/1440,Lists!$E$7:$F$24,2,FALSE)