VLOOKUP Error

Kritty

New Member
Joined
Feb 18, 2019
Messages
4
Hi All,

I am having some problems with my VLOOKUP function and cannot seem to figure out what is going wrong.

My excel formula looks a bit like this:

=VLOOKUP (D1, A1:B151,2,FALSE)

The lookup value in cell D1 = 2

I am trying to search column A for the value of "2" and have it return the corresponding value in column B.

Column A contains values from 1.5 to 3 in increments of 0.01 (1.5, 1.51 etc)

Column B contains values from -2 to -0.8 in increments of 0.008 (eg, -2.0, -1.992 etc)

I am encountering a few problems such as:

- If my lookup value is between 1.5 and 1.63 the function works perfectly, but if I pick a higher lookup value (for example 2) it returns #N/A
- If I use TRUE instead of FALSE at the end of my function I will get an approximate match with any look up value I type in between 1.5 and 3. I find this odd as putting "FALSE" at the end of my VLOOKUP will not work for values greater than 1.63?

The values in columns A and B are formulas. I have tried to copy and paste these values separately but I still get the same #N/A problem occurring when I try to search for specific values.

I've also tried making sure all the cells have the same format (general or number etc). Even used the TRIM tool to try and get rid of any invisible spaces that might be there.

Any help would be greatly appreciated as I am definitely stuck going in circles with this one haha

Thanks :)
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

alpadem

New Member
Joined
Oct 9, 2018
Messages
15
=sumproduct(--(a1:a151=d1),(b1:b151))

Until works 2,26 then broken, i trying to figure out
 
Last edited:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
yes, you're right there is something odd about that, but setting the vlookup() to true should give you the right answer

<b></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;">1</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">-2</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">-2</td><td style="text-align: right;;">-2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1.51</td><td style="text-align: right;;">-1.992</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.55</td><td style="text-align: right;;">-1.96</td><td style="text-align: right;;">-1.96</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1.52</td><td style="text-align: right;;">-1.984</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.6</td><td style="text-align: right;;">-1.92</td><td style="text-align: right;;">-1.92</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1.53</td><td style="text-align: right;;">-1.976</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.65</td><td style="text-align: right;;">-1.976</td><td style="text-align: right;;">-1.976</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">1.54</td><td style="text-align: right;;">-1.968</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.7</td><td style="text-align: right;;">-1.936</td><td style="text-align: right;;">-1.936</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">1.55</td><td style="text-align: right;;">-1.96</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.75</td><td style="text-align: right;;">-1.896</td><td style="text-align: right;;">-1.896</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">1.56</td><td style="text-align: right;;">-1.952</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.8</td><td style="text-align: right;;">-1.856</td><td style="text-align: right;;">-1.856</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1.57</td><td style="text-align: right;;">-1.944</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.85</td><td style="text-align: right;;">-1.816</td><td style="text-align: right;;">-1.816</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1.58</td><td style="text-align: right;;">-1.936</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.9</td><td style="text-align: right;;">-1.776</td><td style="text-align: right;;">-1.776</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">1.59</td><td style="text-align: right;;">-1.928</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.95</td><td style="text-align: right;;">-1.736</td><td style="text-align: right;;">-1.736</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1.6</td><td style="text-align: right;;">-1.92</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.696</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">1.61</td><td style="text-align: right;;">-1.912</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.05</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.656</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">1.62</td><td style="text-align: right;;">-2</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.1</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.616</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">1.63</td><td style="text-align: right;;">-1.992</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.15</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.576</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">1.64</td><td style="text-align: right;;">-1.984</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.2</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.536</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">1.65</td><td style="text-align: right;;">-1.976</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.25</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.496</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">1.66</td><td style="text-align: right;;">-1.968</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.3</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.456</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">1.67</td><td style="text-align: right;;">-1.96</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.35</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.416</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">1.68</td><td style="text-align: right;;">-1.952</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.4</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.376</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">1.69</td><td style="text-align: right;;">-1.944</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.45</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.336</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">1.7</td><td style="text-align: right;;">-1.936</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.5</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.296</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">1.71</td><td style="text-align: right;;">-1.928</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.55</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.256</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;">1.72</td><td style="text-align: right;;">-1.92</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.6</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.216</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;">1.73</td><td style="text-align: right;;">-1.912</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.65</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.176</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;">1.74</td><td style="text-align: right;;">-1.904</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.7</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.136</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;;">1.75</td><td style="text-align: right;;">-1.896</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.75</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.096</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: right;;">1.76</td><td style="text-align: right;;">-1.888</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.8</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.056</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: right;;">1.77</td><td style="text-align: right;;">-1.88</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.85</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-1.016</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style="text-align: right;;">1.78</td><td style="text-align: right;;">-1.872</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.9</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-0.976</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style="text-align: right;;">1.79</td><td style="text-align: right;;">-1.864</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.95</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-0.936</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style="text-align: right;;">1.8</td><td style="text-align: right;;">-1.856</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">-0.896</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)">Sheet3</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)">E1</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$D1,A:B,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F1</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$D1,A:B,2</font>)</td></tr></tbody></table></td></tr></table><br />
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
it's a rounding off issue, this will work

Code:
=SUMPRODUCT(--(ROUND($A$1:$A$151,2)=D1),($B$1:$B$151))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,328
Members
409,863
Latest member
stacy09
Top