Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get correct results when LOOKUP with calculated numbers
I am using LOOKUP and VLOOKUP to lookup data on tables. I have done this
successfully, but I get unusual results when looking up calculated values. Sometimes the lookup results are correct sometimes not. All values are numbers, lookup_vector is in descending order. Lookup works when value is typed in cell not calculated. Unfortunately I need to lookup the value calculated from a lookup value. calculation is simple ie =D2+0.1 I am specifically looking up the values less than and greater than the lookup_value. I have been looking up the less than value then adding 0.1 to get the greater than value. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get correct results when LOOKUP with calculated numbers
It's often the case that calculated values are formatted to two decimal
places, so for instance 10 divided by three shows in the cell as 3.33 but the actual value is 3.33333333 etc so if your VLOOKUP is looking for 3.33 it won't find it. Try using =ROUND(Your Formula,2) to round to two decimal places, Regards, Alan. "onthemountain" wrote in message ... I am using LOOKUP and VLOOKUP to lookup data on tables. I have done this successfully, but I get unusual results when looking up calculated values. Sometimes the lookup results are correct sometimes not. All values are numbers, lookup_vector is in descending order. Lookup works when value is typed in cell not calculated. Unfortunately I need to lookup the value calculated from a lookup value. calculation is simple ie =D2+0.1 I am specifically looking up the values less than and greater than the lookup_value. I have been looking up the less than value then adding 0.1 to get the greater than value. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get correct results when LOOKUP with calculated numb
Thanks Alan,
That fixed my problem. I'm not sure why this was a problem because all calculations were simple addition, so 4.110 was different than 4.11. It works now I'm happy. thanks again "Alan" wrote: It's often the case that calculated values are formatted to two decimal places, so for instance 10 divided by three shows in the cell as 3.33 but the actual value is 3.33333333 etc so if your VLOOKUP is looking for 3.33 it won't find it. Try using =ROUND(Your Formula,2) to round to two decimal places, Regards, Alan. "onthemountain" wrote in message ... I am using LOOKUP and VLOOKUP to lookup data on tables. I have done this successfully, but I get unusual results when looking up calculated values. Sometimes the lookup results are correct sometimes not. All values are numbers, lookup_vector is in descending order. Lookup works when value is typed in cell not calculated. Unfortunately I need to lookup the value calculated from a lookup value. calculation is simple ie =D2+0.1 I am specifically looking up the values less than and greater than the lookup_value. I have been looking up the less than value then adding 0.1 to get the greater than value. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup with multiple results, without duplicates | Excel Worksheet Functions | |||
Can Function results be calculated? | New Users to Excel | |||
Lookup Returning Multiple Results | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Alphabetical list of Numbers are not correct! | Excel Discussion (Misc queries) |