Using TriLookup

3. TriLookup Functions Feature Highlights

TVLOOKUP (TVLKP) and THLOOKUP (THLKP) functions are enhanced versions of Excel’s VLOOKUP and HLOOKUP built-in functions. They allow you to assign any column or row of a rectangular cell range as a lookup and return row/column, by simply specifying the row/column title or its number (index).
T2LOOKUP and T2LKP functions allow you to retrieve values from 2D tables that have two independent variables, X and Y.
T3LOOKUP and T3LKP functions allow you to retrieve values from 3D tables that have three independent variables, X, Y and Z.
Depending on the values of optional parameters, all TriLookup functions listed above can calculate the return value by interpolating or extrapolating table values.
Several modes of interpolation are available including closest value lookup, linear interpolation, curve interpolation using parabolic and hyperbolic piecewise curves, cubic splines and polynomial curves of order up to 20.
TriLookup functions can process tables with missing and invalid values by either returning #N/A values in the areas of missing data, or by interpolating and extrapolating from the valid table values.
TVPOLYDATA and THPOLYDATA functions allow you to easily determine polynomial curve coefficients, with order up to 20. The polynomial curves are fitted through the X-Y data points given in a multi-column or a multi-row table, using the least squares method.
The TVPOLYDATA and THPOLYDATA functions automatically determine the best fit polynomial curve, with an option of taking into account the smoothness of the curve between the X-Y data points.
TPOLY function calculates the return value (Y) of a Y = f(X) polynomial curve for a given lookup value (X) and an array of polynomial curve coefficients. Optionally, it can return the value of a derivative (1^st, 2^nd, 3^rd, etc) for the given lookup value (X).
In case of an error, all TriLookup functions (except TPOLY) can either return a standard Excel error code (such as #NA or #VALUE!), or a detailed text error message indicating the nature of the error and pointing to the cause of it. This feature can be very helpful when debugging your spreadsheet.

Previous 1 2 3 4 5 6 Next