lookuprange

lookuprange(`value`

,`rangelist`

,`valuelist`

)

The LOOKUPRANGE function has the following argument:

`value`

The value to use for the range lookup.

`rangelist`

The list of numeric values that are the range boundaries.

`valuelist`

The list of values, one matching each range, that are options that can be returned.

This function extracts a value by comparing the numeric value to a list of range breaks, and returning a value from a corresponding position in the value list.

The value argument must be a numeric value. This value will be compared against the numbers in the range list.

The rangelist argument must be a list of numeric values sorted in to ascending order.

The value argument will be compared to each range list value in turn. The search will stop at the last entry where the key value is less than or equal to the list value, or at the end of the list. If the key is greater than the last entry in the list, then the last entry will be used. The value to returned is from the matching position in the value list.

The number of values in the result list must be the same as the number of values in the valuelist.

Also see the tablerange function which behave similarly but with data tables.

Formula |
Description |
Result |
---|---|---|

lookuprange(0,{1,2,3,4},{'a','b','c','d'}) |
Search the value list for the last entry that is less than or equal to 2, and return the corresponding value from the result list. 0 is less than the first value list entry, so the first result entry is returned. |
'a' |

lookuprange(2,{1,2,3,4},{'a','b','c','d'}) | Search the value list for the last entry that is less than or equal to 2, and return the corresponding value from the result list. 2 is equal to the second value list entry, so the second result entry is returned. |
'b' |

lookuprange(99,{1,2,3,4},{'a','b','c','d'}) |
Search the value list for the last entry that is less than or equal to 99, and return the corresponding value from the result list. 99 is greater than the last entry in the value list, so the last result entry is returned. |
'd' |