TriLookup Examples

3. T3LOOKUP & T3LKP

[Click here to see a screen capture of T3LOOKUP interactive example]

T3LOOKUP Single Area 3D Table Examples

The 21-row by 4-column lookup table below is set up for use by the T3LOOKUP function. It is a single area 3D table containing 3 component 2D tables, separated by 2 separator rows (Table_Ygap = 2). Each 2D table has 5 rows (Table_Ysize = 5). The first (topmost) 2D table contains X-axis values in its topmost row and Y-axis values in its leftmost column, while the topmost rows and the leftmost columns of the remaining 2D tables are ignored. The Z-axis values for each 2D table are located in the top-left corner cells. The return values, which depend on X, Y and Z, are in the remaining area.

Search for X value = 3.5, Y value = 0.9 and Z value = 1.25 in the single area 3D (X-Y-Z) lookup table and determine the V(x,y,z) return value; use the "closest value" matching along X, Y and Z axes (X/Y/Z_interpolate = 3).

=T3LOOKUP(3.5,0.9,1.25,$B$9:$E$29,2,5,3,3,3,,,,,,,,,TRUE) equals: 0.03

Same as above, but use a linear interpolation along X, Y and Z axes (X/Y/Z_interpolate = 4).

=T3LOOKUP(3.5,0.9,1.25,$B$9:$E$29,2,5,4,4,4,,,,,,,,,TRUE) equals 0.25375

Same as above, but use a double parabolic piecewise curve interpolation along the X-axis (X_interpolate = 5), a polynomial curve interpolation with order n = 3 along the Y-axis (Y_interpolate = -3) and a cubic spline curve interpolation along the Z-axis (Z_interpolate = 7).

=T3LOOKUP(3.5,0.9,1.25,$B$9:$E$29,2,5,5,-3,7,,,,,,,,,TRUE) equals 0.064854531

Search for X value = 3.5, Y value = 0.9 and Z value = 2.5 in the single area 3D (X-Y-Z) lookup table and determine the V(x,y,z) return value; use a linear interpolation along X, Y and Z axes (X/Y/Z_interpolate = 4).

=T3LOOKUP(3.5,0.9,2.5,B9:E29,2,5,4,4,4,,,,,,,,,TRUE) equals:
#N/A {Err.311} Invalid Z_value = 2.5 (out of bounds: 0.5 to 2).

Same as above, but add Z_extrapolate = 0.5 to allow T3LOOKUP to extrapolate up to 0.5 units past the minimum and maximum Z-axis values.

=T3LOOKUP(3.5,0.9,1.25,B9:E29,2,5,4,4,4,,,0.5,,,,,,TRUE) equals 0.25375

T3LOOKUP & T3LKP Multi Area 3D Table Examples

The three 5-row by 4-column tables below together make up a multi area 3D lookup table, set up for use by the T3LOOKUP and T3LKP functions. Note that the individual 2D tables don't have to be in order and can be scattered throughout the worksheet. The first (topmost) 2D table contains X-axis values in its topmost row and Y-axis values in its leftmost column, while the topmost rows and the leftmost columns of the remaining 2D tables are ignored. The Z-axis values for each 2D table are located in the top-left corner cells. The return values, which depend on X, Y and Z are in the remaining areas. Note that, when specifying a multi area table as the Table_XYZ parameter, you must enclose it in parentheses. Otherwise, it will be interpreted as more than one parameter, which will cause an error.

Search for X value = 3.5, Y value = 0.9 and Z value = 1.25 in the multi area 3D (X-Y-Z) lookup table and determine the V(x,y,z) return value; use a linear interpolation along X, Y and Z axes (Interpolate = 4).

=T3LKP(3.5,0.9,1.25,(B60:E64,C66:F70,D72:G76),4,TRUE) equals 0.25375

Same as above, but specify only the top left cells for the second and third 2D table. T3LOOKUP and T3LKP assume that all 2D tables are of the same size in X and Y directions (have the same number of rows and columns) as the first one.

=T3LKP(3.5,0.9,1.25,(B60:E64,C66,D72),4,TRUE) equals 0. 25375

Same as above, but use different types of interpolation in the X, Y and Z directions: X_interpolate = 4 (linear), Y_interpolate = 7 (spline) and Z_interpolate = 5 (double parabolic). For this you must use the T3LOOKUP function.

=T3LOOKUP(3.5,0.9,1.25,(B60:E64,C66,D72),,,4,7,5,,,,,,,,,TRUE) equals 0.059425

Same as above, but the multiple area range specified as the Table_XYZ parameter not enclosed it in parentheses. T3LOOKUP returns the Excel #VALUE! error code because too many parameters are specified.

=T3LOOKUP(3.5,0.9,1.25,B60:E64,C66,D72,,,4,7,5,,,,,,,,,TRUE) equals #VALUE!

T3LOOKUP Skewed 3D Table Examples

The two 3D tables below demonstrate how you can use T3LOOKUP to retrieve information from skewed 3D tables in which the component 2D tables have different values along X and Y axes. Note that many of the return values in the data area of the modified table are left blank. Therefore, you must set Missing_pts = TRUE. In some cases it may also be necessary to enable extrapolation by assigning X/Y/Z_extrapolate parameters non zero values.

Search for X value = 5, Y value = 0.9 and Z value = 1.25 in the single area 3D (X-Y-Z) lookup table and determine the V(x,y,z) return value; use a double parabolic piecewise curve interpolation along X, Y and Z axes (X/Y/Z_interpolate = 5); set Missing_pts = TRUE

=T3LOOKUP(5.7,0.9,1.25,$A$121:$G$138,0,6,5,5,5,,,,,,,,TRUE,TRUE) equals
-0.1000785

Search for X value = 4.2, Y value = 0.9 and Z value = 1.25 in the single area 3D (X-Y-Z) lookup table and determine the V(x,y,z) return value; use linear interpolation along X, Y and Z axes (X/Y/Z_interpolate = 4); set Missing_pts = TRUE

=T3LOOKUP(4.2,0.9,1.25,$A$121:$G$138,0,6,4,4,4,,,,,,,,TRUE,TRUE) equals:
#N/A {Err.421} Invalid lookup point [X_value = 4.2, Y_value = 0.9, Z_value = 1.25], (out of bounds due to missing or invalid cells in data area).

Same as above, but allow extrapolation up to 1.5 units along X, Y and Z axes (X/Y/Z_extrapolate = 1.5).

=T3LOOKUP(4.2,0.9,1.25,$A$121:$G$138,0,6,4,4,4,1.5,1.5,1.5,,,,,TRUE,TRUE) equals 0.3221

Previous 1 2 3 4 Next