Simple vlookup gremlin

[Jordo82]

Hi All,

I've got a table with percentages running down the left side that I want to do a vlookup on. The percentages are in increments of 1, beginning at 4.1%. So I have 4.1, 5.1, 6.1 etc.

I'm using the round function to get integer results and then adding on the extra .1% to do the lookup. So my formula looks like VLOOKUP(ROUND(A1,2)+0.001,R1:T40,2,false) where A1 contains a random percentage value.

This works fine except for the value of 14.1%, which produces a N/A error on the vlookup function. I've tried re-entering 14.1% into the table. I've verified that my round function results in 14.1% by checking ROUND(A1,2)+0.001 = .141 and the result is true. The formula auditor shows nothing wrong until vlookup executes. I'm at a total loss, any help is appreciated! Thanks.

 

[Jordo82]

Follow up: it's not even the round function.

VLOOKUP(.141.... works but
VLOOKUP(.14+.001... doesn't.

Again, this is only for 14.1%. It works fine for 15.1, 16.1, etc. ARG!

 

[Andrew Poulsom]

Try:

=VLOOKUP(ROUND(ROUND(A1,2)+0.001,3),R1:T40,2,FALSE)

 

[mtb'r]

You could also try:

=VLOOKUP(ROUNDUP(A1+0.001,2),R1:T40,2,TRUE)

Using "TRUE" as the final term will cause Excel to find the closest match, without going over your first term. If A = 14%, adding the .001 to it will cause it to be rounded up to 15%. The closest match without exceeding 15% would be 14.1%.

 

[Jordo82]

Perfect Andrew, thanks so much!

Must have been a tiny decimal being added in error... **** floating points.