vlookup performs inconsistently with decimals in lookup value form

[Oxo]

The problem deals with using a simple formula to determine the lookup value
in vlookup, if the formula (result) involves a decimal.

In EXCEL 2007, I have a table with row numbers and labels. I have another
table with row numbers that are a consistent increment higher than the first
table, e.g. 1st table row number +100 = 2nd table row number.

I want to use vlookup to retrieve the corresponding labels from table 1. For
example A4 = 11, B4 = Tomatoes. A54 = 111. To retrieve the B4 label,
Tomatoes, the formula is vlookup(a54-100,$a$1:$b$25,1,0) This works.

Vlookup will return the correct label for a lookup value of (112.5-100).
However, it will return N/A for lookup value of (112.1-100), but will return
the label if the lookup value is entered directly as 12.1.

Varying the increment results in SOMETIMES vlookup returns the label,
sometimes it returns N/A. I found that having a decimal value in either the
increment or in the initial row number makes the formula work or not work on
an apparently random basis.

Try it on a table from 12.1 to 13.5 in increments of 0.1 (12.1, 12.2, 12.3
etc.) and another table numbered 112.1 to 113.5 also in increments of 0.1.
For me, it will find a match for (112.5 -100), will not find a match for
(112.6 -100), but will for 12.6.

I think this is a flaw in EXCEL.

 

[www.exciter.gr: Custom Excel Applications!]

To see an explanation for this, go to an empty sheet and do the
following:

1. complete range A1 to A9 with values from 5.1 to 5.9
2. complete range B1 to B9 with values from 105.1 to 105.9
3. input formula =TRIM(A1) in cell C1 and drag to C9
4. input formula =TRIM(B1-100) in cell D1 and drag to D9

you will notice that values in columns C and D do not always match,
although they should. Dont know if this is due to excel or proccessor.

Anyway, for your problem, if your data are sorted then use vlookup
along with round (to 1 decimal) and set vlookup's last parameter
(range lookup) to 1 instead of 0

example:
=VLOOKUP((ROUND(D2-100,1)),A:B,2,1)

It worked for me with sorted data in column A

Hope this helps

http://www.exciter.gr
Custom Excel Applications and Functions.

 

[Roger Govier]

Hi

The problem is caused because many numbers cannot be represented accurately
in binary.
If you use the Round function, then the Vlookup will work

=VLOOKUP(ROUND(A54-100,1),$A$1:$B$25,2,0)

Note: I assume the ,1,0 in your posted formula was a mistype, as ,1 would
have returned the number in column A not the value of Tomatoes from column
B.