|Range Lookup family|
|Functions for performing a lookup where a value is dependent on a lookup table of values.|
|Description: Do various forms of Lookup and Interpolation using input values and range.|
|Introduction: All the functions in this category are for use with numbers. This is partly deliberate, partly just habit and intuition. We feel safer dealing with numbers, and like to occasionally take advantage the fact that text input is in general read as a zero in Business Functions. We just think that functions that just accept numbers and just return numbers are slightly more bullet-proof ? you tell us whether you think we?re right.|
Amongst the straightforward Lookups, LookupNum is our version of Excels LOOKUP function. Where it differs from Excel is that only accepts 1 dimensional ranges (lists), but more importantly it has a LookupSwitch, which defines how the lookup is carried out. The default is the same as Excel: starting at the top of the InputRange (where the values are highest), the lookup function finds the first value that is less than OR equal to the InputValue and returns the corresponding value in the OutputRange. Of course you don"t have to start at the top of the range of values, and you don"t have to test for less or equal - there are other ways to lookup, for example you could do it exactly the other way round. You could start at the bottom of the range (where the values are lowest) and look for the first time that a value in the InputRange is higher than or equal to the InputValue. With all this talk of the top and bottom of the range, you might be wondering if the function sorts the date first - well, no it doesn"t, but it does check for consistency and it does detect what direction (ascending, descending) the range is going in.
Another kind of lookup is where you just want to test for an exact match, and it could be anywhere within the InputRange, even if the range is not in ascending or descending order. This is what SeekNum does, and is useful whene you really only want to a look up for an exact match.
Then there is the occasion where you just want to pick the n-th value from a list, like INDEX does in Excel. Business Functions provides PickNum, a very simple function for just picking a number. A very useful and powerful relation is SortPick, which sorts the data (heap sort) before doing the pick ? this is doing quite a lot of work, if you think about it, because it sorts the data before picking ? so this way you can pick the 5th highest from a list, etc. This concept of a function doing a sort in real time instead of having to do a sort from the Excel menu is taken further with the array function SortNum. You can read more about Array Functions elsewhere, but basically that return an entire list of numbers that you can ?spray? onto your worksheet. Then whenever the data changes, you can get an automatically resorted list.
Completing the lookup functions we have two functions for looking up in 2 dimensional circumstances. LookUpNum2D is the 2 dimensional lookup equivalent of LookupNum, where you have an x (horizontal) and a y (vertical) value to look up against. Similarly, PickMatrix, which picks a number out of a 2-D matrix, is the 2-D equivalent of PickNum.
|Functions in the Range Lookup family (12)|