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

zakizelani

New Member
Joined
Mar 3, 2016
Messages
25
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:00Elephant
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

<tbody>
</tbody>

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????? :confused::confused::confused:
vlookup.JPG
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
ABCDEF
710:30:00AntAnt10:30:00Ant
811:00:00BearBear11:00:00Bear
911:30:00#N/ABear11:30:00Cat
1012:00:00#N/ACat12:00:00Dolphin
1112:30:00#N/ADolphin12:30:00Elephant
1213:00:00FoxFox13:00:00Fox
1313:30:00GorillaGorilla13:30:00Gorilla
1414:00:00HorseHorse14:00:00Horse
1514:30:00#N/AIguana14:30:00Iguana
1615:00:00#N/AJaguar15:00:00Jaguar
1715:30:00#N/AKangaroo15:30:00Kangaroo
1816:00:00#N/ALeopard16:00:00Leopard
1916:30:00#N/AMouse16:30:00Mouse
2017:00:00#N/AScorpion17:00:00Scorpion
2117:30:00#N/AOctupus17:30:00Octupus
2218:00:00#N/APeguin18:00:00Peguin
2318:30:00#N/ATiger18:30:00Tiger
2419:00:00#N/ARabbit19:00:00Rabbit
Sheet1
Cell Formulas
RangeFormula
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
ABCDEF
710:30:00AntAnt10:30:00Ant
811:00:00BearBear11:00:00Bear
911:30:00CatCat11:30:00Cat
1012:00:00DolphinDolphin12:00:00Dolphin
1112:30:00ElephantElephant12:30:00Elephant
1213:00:00FoxFox13:00:00Fox
1313:30:00GorillaGorilla13:30:00Gorilla
1414:00:00HorseHorse14:00:00Horse
1514:30:00IguanaIguana14:30:00Iguana
1615:00:00JaguarJaguar15:00:00Jaguar
1715:30:00KangarooKangaroo15:30:00Kangaroo
1816:00:00LeopardLeopard16:00:00Leopard
1916:30:00MouseMouse16:30:00Mouse
2017:00:00ScorpionScorpion17:00:00Scorpion
2117:30:00OctupusOctupus17:30:00Octupus
2218:00:00PeguinPeguin18:00:00Peguin
2318:30:00TigerTiger18:30:00Tiger
2419:00:00RabbitRabbit19:00:00Rabbit
Sheet1
Cell Formulas
RangeFormula
B9=VLOOKUP(A9,$E$7:$F$24,2,0)
C9=VLOOKUP(A9,$E$7:$F$24,2,1)
 
Upvote 0
hi alan Y , thanks for replying.

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

however can you explain the second image? (solution?)
 
Upvote 0
would like to know how do you solve it.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top