Hlookup, range_lookup parameter

L

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
 
P

Pete_UK

Instead of:

=HLOOKUP(lookup_value,etc ...

use:

=HLOOKUP(lookup_value+0.5,etc ...

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top