Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hlookup, range_lookup parameter
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hlookup, range_lookup parameter
Instead of:
=HLOOKUP(lookup_value,etc ... use: =HLOOKUP(lookup_value+0.5,etc ... Hope this helps. Pete On Dec 2, 11:28*pm, lmagnuss wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to choose if I use a parameter or not in a parameter query | Excel Discussion (Misc queries) | |||
VLOOKUP returning value when range_lookup false | Excel Worksheet Functions | |||
VLOOKUP(lookup_value, ___ ,col_index_num,range_lookup) | Excel Worksheet Functions | |||
range_lookup in lookup functions | Excel Worksheet Functions | |||
Cell contents as the 'lookup value' parameter in HLOOKUP function | Excel Discussion (Misc queries) |