Excel lookup
-
I'm trying to lookup a fractinal value in a column in an Excel spreadsheet (not in code). VLOOKUP usually does the job nicely, but in this case the value may not be in the column, so I want to find the nearest value to the one I'm looking up. VLOOKUP wil return the nearest value less than the fractional number, but I want to find the nearest in either direction. Can anyone offer any suggestions? Basically, looking up 1.37 in { 1.1, 1.2, 1.3, 1.4 } returns 1.3, but I want it to return 1.4. Thanks
Ryan
"Punctuality is only a virtue for those who aren't smart enough to think of good excuses for being late" John Nichol "Point Of Impact"
-
I'm trying to lookup a fractinal value in a column in an Excel spreadsheet (not in code). VLOOKUP usually does the job nicely, but in this case the value may not be in the column, so I want to find the nearest value to the one I'm looking up. VLOOKUP wil return the nearest value less than the fractional number, but I want to find the nearest in either direction. Can anyone offer any suggestions? Basically, looking up 1.37 in { 1.1, 1.2, 1.3, 1.4 } returns 1.3, but I want it to return 1.4. Thanks
Ryan
"Punctuality is only a virtue for those who aren't smart enough to think of good excuses for being late" John Nichol "Point Of Impact"
Can you round 1.37 up to 1.4 and then do the lookup?
"The largest fire starts but with the smallest spark." - David Crow
"Judge not by the eye but by the heart." - Native American Proverb
-
Can you round 1.37 up to 1.4 and then do the lookup?
"The largest fire starts but with the smallest spark." - David Crow
"Judge not by the eye but by the heart." - Native American Proverb
DavidCrow wrote:
Can you round 1.37 up to 1.4 and then do the lookup?
Not really. Some of the numbers are not in the list, such as 1.7, so if I ended up with a number like 1.73, it would get rounded down to 1.7 and the lookup would return 1.6 (as there is no 1.7), but it should be returning 1.8. I fudged a solution by looking up the number, and then using IF() statements to compare the looked up value and the one above it to see which was closest to the actual value. It's a real kludge, but does the job ok. I was just wondering if excel provided a neater solution.
Ryan
"Punctuality is only a virtue for those who aren't smart enough to think of good excuses for being late" John Nichol "Point Of Impact"