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:

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

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

 

[AlanY]

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]

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)