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. |
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) |