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

zakizelani

New Member
Joined
Mar 3, 2016
Messages
24
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
a very weird problem, I've reproduced your data here with both vlookup()s produced wrong results for 11:30:00 etc

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">10:30:00</td><td style=";">Ant</td><td style=";">Ant</td><td style="text-align: right;;"></td><td style="text-align: right;;">10:30:00</td><td style=";">Ant</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">11:00:00</td><td style=";">Bear</td><td style=";">Bear</td><td style="text-align: right;;"></td><td style="text-align: right;;">11:00:00</td><td style=";">Bear</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">11:30:00</td><td style="text-align: right;;">#N/A</td><td style=";">Bear</td><td style="text-align: right;;"></td><td style="text-align: right;;">11:30:00</td><td style=";">Cat</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">12:00:00</td><td style="text-align: right;;">#N/A</td><td style=";">Cat</td><td style="text-align: right;;"></td><td style="text-align: right;;">12:00:00</td><td style=";">Dolphin</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">12:30:00</td><td style="text-align: right;;">#N/A</td><td style=";">Dolphin</td><td style="text-align: right;;"></td><td style="text-align: right;;">12:30:00</td><td style=";">Elephant</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">13:00:00</td><td style=";">Fox</td><td style=";">Fox</td><td style="text-align: right;;"></td><td style="text-align: right;;">13:00:00</td><td style=";">Fox</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">13:30:00</td><td style=";">Gorilla</td><td style=";">Gorilla</td><td style="text-align: right;;"></td><td style="text-align: right;;">13:30:00</td><td style=";">Gorilla</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">14:00:00</td><td style=";">Horse</td><td style=";">Horse</td><td style="text-align: right;;"></td><td style="text-align: right;;">14:00:00</td><td style=";">Horse</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">14:30:00</td><td style="text-align: right;;">#N/A</td><td style=";">Iguana</td><td style="text-align: right;;"></td><td style="text-align: right;;">14:30:00</td><td style=";">Iguana</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">15:00:00</td><td style="text-align: right;;">#N/A</td><td style=";">Jaguar</td><td style="text-align: right;;"></td><td style="text-align: right;;">15:00:00</td><td style=";">Jaguar</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">15:30:00</td><td style="text-align: right;;">#N/A</td><td style=";">Kangaroo</td><td style="text-align: right;;"></td><td style="text-align: right;;">15:30:00</td><td style=";">Kangaroo</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">16:00:00</td><td style="text-align: right;;">#N/A</td><td style=";">Leopard</td><td style="text-align: right;;"></td><td style="text-align: right;;">16:00:00</td><td style=";">Leopard</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">16:30:00</td><td style="text-align: right;;">#N/A</td><td style=";">Mouse</td><td style="text-align: right;;"></td><td style="text-align: right;;">16:30:00</td><td style=";">Mouse</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">17:00:00</td><td style="text-align: right;;">#N/A</td><td style=";">Scorpion</td><td style="text-align: right;;"></td><td style="text-align: right;;">17:00:00</td><td style=";">Scorpion</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">17:30:00</td><td style="text-align: right;;">#N/A</td><td style=";">Octupus</td><td style="text-align: right;;"></td><td style="text-align: right;;">17:30:00</td><td style=";">Octupus</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">18:00:00</td><td style="text-align: right;;">#N/A</td><td style=";">Peguin</td><td style="text-align: right;;"></td><td style="text-align: right;;">18:00:00</td><td style=";">Peguin</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;">18:30:00</td><td style="text-align: right;;">#N/A</td><td style=";">Tiger</td><td style="text-align: right;;"></td><td style="text-align: right;;">18:30:00</td><td style=";">Tiger</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;">19:00:00</td><td style="text-align: right;;">#N/A</td><td style=";">Rabbit</td><td style="text-align: right;;"></td><td style="text-align: right;;">19:00:00</td><td style=";">Rabbit</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A9</th><td style="text-align:left">=A8+0.5/24</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,$E$7:$F$24,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,$E$7:$F$24,2,1</font>)</td></tr></tbody></table></td></tr></table><br />


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

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;background-color: #E2EFDA;;">10:30:00</td><td style=";">Ant</td><td style=";">Ant</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">10:30:00</td><td style=";">Ant</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;background-color: #E2EFDA;;">11:00:00</td><td style=";">Bear</td><td style=";">Bear</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">11:00:00</td><td style=";">Bear</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;background-color: #E2EFDA;;">11:30:00</td><td style=";">Cat</td><td style=";">Cat</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">11:30:00</td><td style=";">Cat</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;background-color: #E2EFDA;;">12:00:00</td><td style=";">Dolphin</td><td style=";">Dolphin</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">12:00:00</td><td style=";">Dolphin</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;background-color: #E2EFDA;;">12:30:00</td><td style=";">Elephant</td><td style=";">Elephant</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">12:30:00</td><td style=";">Elephant</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;background-color: #E2EFDA;;">13:00:00</td><td style=";">Fox</td><td style=";">Fox</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">13:00:00</td><td style=";">Fox</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;background-color: #E2EFDA;;">13:30:00</td><td style=";">Gorilla</td><td style=";">Gorilla</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">13:30:00</td><td style=";">Gorilla</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;background-color: #E2EFDA;;">14:00:00</td><td style=";">Horse</td><td style=";">Horse</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">14:00:00</td><td style=";">Horse</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;background-color: #E2EFDA;;">14:30:00</td><td style=";">Iguana</td><td style=";">Iguana</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">14:30:00</td><td style=";">Iguana</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;background-color: #E2EFDA;;">15:00:00</td><td style=";">Jaguar</td><td style=";">Jaguar</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">15:00:00</td><td style=";">Jaguar</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;background-color: #E2EFDA;;">15:30:00</td><td style=";">Kangaroo</td><td style=";">Kangaroo</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">15:30:00</td><td style=";">Kangaroo</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;background-color: #E2EFDA;;">16:00:00</td><td style=";">Leopard</td><td style=";">Leopard</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">16:00:00</td><td style=";">Leopard</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;background-color: #E2EFDA;;">16:30:00</td><td style=";">Mouse</td><td style=";">Mouse</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">16:30:00</td><td style=";">Mouse</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;background-color: #E2EFDA;;">17:00:00</td><td style=";">Scorpion</td><td style=";">Scorpion</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17:00:00</td><td style=";">Scorpion</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;background-color: #E2EFDA;;">17:30:00</td><td style=";">Octupus</td><td style=";">Octupus</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17:30:00</td><td style=";">Octupus</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;background-color: #E2EFDA;;">18:00:00</td><td style=";">Peguin</td><td style=";">Peguin</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">18:00:00</td><td style=";">Peguin</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;background-color: #E2EFDA;;">18:30:00</td><td style=";">Tiger</td><td style=";">Tiger</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">18:30:00</td><td style=";">Tiger</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;background-color: #E2EFDA;;">19:00:00</td><td style=";">Rabbit</td><td style=";">Rabbit</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">19:00:00</td><td style=";">Rabbit</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,$E$7:$F$24,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,$E$7:$F$24,2,1</font>)</td></tr></tbody></table></td></tr></table><br />
 

zakizelani

New Member
Joined
Mar 3, 2016
Messages
24
hi alan Y , thanks for replying.

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

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

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
would like to know how do you solve it.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

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
Top