Hlookup, range_lookup parameter

[lmagnuss]

Hi all.

I know the standard definition for the range_lookup parameter of the Hlookup
formula is

"If TRUE or omitted, an approximate match is returned. In other words, if an
exact match is not found, the next largest value that is less than
lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one
is not found, the error value #N/A is returned."

My problem is with the "TRUE" statement. What I'd like Hlookup to do if TRUE
is to find a value within a given range, be it larger or smaller... say, + or
- 0.5

my table looks like this:

Sugar Cellobiose Glucose Xylose Galactose Arabinose Mannose
12.47 14.91 16.02 16.87 18.11 18.62
2 16742.7 9398.85 7399.1 4170.5 3950.8 3844.7
5 42249.5 22839.1 18381.2 10712 9672.3 10447.5
10 84724.9 44193.1 36103.5 20695.8 19420.9 21502

and my lookup value, for example, would be 12.408. Because this value is
smaller than any in the 2nd row, Hlookup returns #N/A. Also, if my lookup
value was 15.99, Hlookup would return values from the "glucose" column, as
opposed to the "xylose" column... even though 15.99 is closer to 16.02 than
14.91. Does anyone here know if there is any way to customize the way Hlookup
finds values?

Thanks a bunch!!
~Lauren

 

[Pete_UK]

Instead of:

=HLOOKUP(lookup_value,etc ...

use:

=HLOOKUP(lookup_value+0.5,etc ...

Hope this helps.

Pete