Vlookup 8.31 problem

[dragonarss]

Can anybody tell me why this is happening?

<table width="256" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64" align="center">#N/A</td> <td style="width:48pt" width="64" align="right">8.31</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64" align="right">6.31
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td align="right">6.31</td> <td align="right">-66</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td align="right">7.31</td> <td align="right">89</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td align="right">8.31</td> <td align="right">244</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td align="right">9.31</td> <td align="right">399</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td align="right">10.31</td> <td align="right">554</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td align="right">11.31</td> <td align="right">709</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td align="right">12.31</td> <td align="right">864</td> <td>
</td> </tr> </tbody></table>
The cell A2 Contains The formula =VLOOKUP(B2,B4:C10,2,FALSE)
Cell D2 contains the number for the range in B4 to start at.
Cell B2 Contains the lookup value 8.31.
B Column formula is just B4+1 etc ascending to B10.
Vlookup will find 6.31 & 7.31 but returns #N/A in A2 for anything after.
If you change D2 to 6.32 Vlookup will return all of the correct results.

Any info would be much appreciated.

 

[VoG]

It works for me. Make sure that the 8.31 in the table is exactly 8.31 and not a number like 8.310001 formatted to display 2 decimal places

Excel Workbook
	A	B	C
2	244	8.31
3
4		6.31	-66
5		7.31	89
6		8.31	244
7		9.31	399
8		10.31	554
9		11.31	709
10		12.31	864
Sheet2

 

[Aladin Akyurek]

You probably need TRUE (1), not FALSE...

=VLOOKUP(B2,B4:C10,2,1)

 

[dragonarss]

VoG Thanks for the speedy reply,
Can I just confirm that the formula you have in B6 is B5+1?
I have also tried formatting to 30 decimal places and there are no extra numbers.
This was entered on a new sheet without any formatting.

 

[VoG]

With that formula I get #N/A. Try Aladin's suggestion which works for me

=VLOOKUP(B2,B4:C10,2,TRUE)

 

[dragonarss]

You probably need TRUE (1), not FALSE...

=VLOOKUP(B2,B4:C10,2,1)

Thanks Aladin I have tried this and it does work but It should work with False...

 

[dragonarss]

With that formula I get #N/A. Try Aladin's suggestion which works for me

=VLOOKUP(B2,B4:C10,2,TRUE)

Thanks Vog Its crazy because it works if you change 6.31 to 6.32.

 

[Aladin Akyurek]

Thanks Aladin I have tried this and it does work but It should work with False...

If B4:B10 are e.g. calculated values, they could have differing decimal parts.

You could check this with:

=8.31=B6

assuming that B6 is supposed to house 8.31.

By the way, since B4:B10 is in ascending order, setting the match-type to 1 is the right thing to do.

 

[dragonarss]

If B4:B10 are e.g. calculated values, they could have differing decimal parts.

You could check this with:

=8.31=B6

assuming that B6 is supposed to house 8.31.

By the way, since B4:B10 is in ascending order, setting the match-type to 1 is the right thing to do.

Ok mate thanks will do, Its strange that if you use any other value in D2 it works with false its just that one number (6.31).... Spun me out a little

Thanks again.

 

[Aladin Akyurek]

Ok mate thanks will do, Its strange that if you use any other value in D2 it works with false its just that one number (6.31).... Spun me out a little

Thanks again.

You are welcome. Thanks for providing us feedback.