Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup performs inconsistently with decimals in lookup value form
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup performs inconsistently with decimals in lookup valueform
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. On Nov 16, 8:54 pm, Oxo wrote: 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup performs inconsistently with decimals in lookup value form
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create a drop down list that performs a task | Excel Discussion (Misc queries) | |||
filling a form in Excel... lookup? maybe? | Excel Discussion (Misc queries) | |||
Free Software that Performs 3D XYZ Plotting w Excel Data? | Excel Discussion (Misc queries) | |||
How to change macro so it performs actions on ACTIVE sheet? | Excel Discussion (Misc queries) | |||
Performs illegal operation on save | Excel Discussion (Misc queries) |