[SORT OF SOLVED]VLOOKUP formula query

[thedaddy]

Hi,

I have a problem with the VLOOKUP function and i can't work out what is going on. I've attatched file.

The program keeps track of scores and handicaps in a golf society

On the members sheet in cell F9, the result should equal 13.2, but the formula throws out 13.0. The final VLOOKUP seems to be the problem. The lookup value is F8(which is 13.4), the table array is named 'calc'(on the tables sheet), and column index number works out to be 3. The figure in column 3 is 13.2, yet it throws out 13.0!

Any ideas why, it may be staring me in the face but i can't see it

[martindwilson]

its a rounding problem
try
VLOOKUP(ROUND(F8,2),calc,(E9-ss5)+2,FALSE)
note you should use the false arguement for exact match if not
VLOOKUP(ROUND(F8,2),calc,(E9-ss5)+2,true) leaving it of is the same as "true"

[thedaddy]

Thanks that works a treat!

Can you walk me through it as i don't understand why it happened. I've not come across this problem before? Why does rounding f8 to 2 places correct the problem.

I've never noticed this happen before, is it something to do with this particular cell as it's not happening in similar circumstances on the rest of the sheet??

[martindwilson]

no idea but if you just do a simple vlookup
VLOOKUP(F8,calc,3,FALSE) you get an error
replace f8 with its value and it works
VLOOKUP(13.4,calc,3,FALSE)
same as
=MATCH(F8,Tables!J66:J71,0) doesnt worh
but
=MATCH(13.4,Tables!J66:J71,0) does
interestingly tho

=F8=Tables!J71 returns true which suggests a match

[thedaddy]

Yes, i tried doing the simple VLOOKUP replacing F8 with the true value, and got exactly same answers to you, that's what completely threw me.

Done a google search and found that if i tick the 'precision as displayed' box in options then it also solves problem. As golf handicaps never go past 1 decimal place having this option ticked should not pose i problem from what i can ascertain