TriLookup Examples

2. T2LOOKUP & T2LKP

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

T2LOOKUP & T2LKP Examples Without Missing and Invalid Cells

The 5-row by 4-column lookup table below is set up for use by the T2LOOKUP and T2LKP functions. It contains X-axis values in the topmost row, Y-axis values in the leftmost column and the return values in the remaining area. The contents of the top left corner cell is ignored.

Search for X value = 3.5 and Y value = 0.9 in the 2D (X-Y) lookup table and determine the V(x,y) return value; use a linear interpolation for both X and Y (Interpolate = 4).

=T2LKP(3.5,0.9,$B$6:$E$10,4) equals 0.611

Search for X value = 5.5 and Y value = 0.9 in the 2D (X-Y) lookup table and determine the V(x,y) return value; use a linear interpolation for both X and Y (Interpolate = 4).

=T2LKP(5.5,0.9,$B$6:$E$10,4) equals #N/A

Same as above, but with Error_msg = TRUE in order to get an explanation on what caused T2LKP to return the #N/A Error.

=T2LKP(5.5,0.9,$B$6:$E$10,4,TRUE) equals:
#N/A {Err.311} Invalid X_value = 5.5 (out of bounds: 3 to 5).

Same as above, but use T2LOOKUP with X_extrapolate = 1. This allows T2LOOKUP to extrapolate up to 1 units away from the last cell in the X direction.

=T2LOOKUP(5.5,0.9,$B$6:$E$10,4,4,1,,,,,,TRUE) equals 0.54

T2LOOKUP Examples With Missing and Invalid Cells

The 5-row by 4-column table below can be used by T2LOOKUP even though it contains missing (blank) and invalid (non numeric) cells.

Search for X value = 3.5 and Y value = 0.9 in the 2D (X-Y) lookup table and determine the V(x,y) return value; use a linear interpolation for both X and Y (4).

=T2LOOKUP(3.5,0.9,$B$29:$E$33,4,4) equals #VALUE!

Same as above, but with Error_msg = TRUE in order to get an explanation on what caused T2LOOKUP to return the #VALUE! Error.

=T2LOOKUP(3.5,0.9,$B$29:$E$33,4,4,,,,,,,TRUE) equals:
#VALUE! {Err.403} 3 missing or invalid cells found in data area (all cells must be numeric for Missing_pts = FALSE).

Same as above, but with Missing_pts = TRUE in order to allow processing of missing (empty) and invalid (non-numeric) cells.

=T2LOOKUP(3.5,0.9,$B$29:$E$33,4,4,,,,,,TRUE,TRUE) equals:
#N/A {Err.409} Invalid lookup point [X_value = 3.5, Y_value = 0.9], (out of bounds due to missing or invalid cells in data area).

Same as above, but with X_extrapolate = 1 and Y_extrapolate = 1. This allows T2LOOKUP to extrapolate up to 1 units away from the last valid cell in both X and Y directions.

=T2LOOKUP(3.5,0.9,$B$29:$E$33,4,4,1,1,,,,TRUE,TRUE) equals 0.6265

Previous 1 2 3 4 Next