TriLookup Examples

1. TVLOOKUP & TVLKP Examples

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

The 6-row by 4-column lookup table below is set up for use by the TVLOOKUP and TVLKP functions. It contains column titles in the topmost row and the lookup and return values in the remaining 5 rows. TVLOOKUP and TVLKP use the column titles in order to identify the lookup column and the return column.

Search for value = 2.75 in the lookup column titled 1 and return a value from the return column "Y1"; use a third order polynomial interpolation (Interpolate = -3).

=TVLKP(2.75,$B$5:$E$10,1,"Y1",-3) equals 0.36819308

Search for value = 0.75 in the lookup column "X2" and return the corresponding value from the return column "Y1"; use a linear interpolation (Interpolate = 4).

=TVLKP(0.75,$B$5:$E$10,"X2","Y1",4) equals 0.317954545

Same as above, but use the column index numbers instead of column titles (3 instead of “X2” and 2 instead of “Y1”) to specify the lookup and return columns (Index_mode = TRUE).

=TVLKP(0.75,$B$5:$E$10,3,2,4,,TRUE) equals 0.317954545

Search for value = 0.75 in the lookup column "X2" and return a value from the return column "Y2"; use a linear interpolation (Interpolate = 4).

=TVLKP(0.75,$B$5:$E$10,"X2","Y2",4) equals #VALUE!

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

=TVLKP(0.75,$B$5:$E$10,"X2","Y2",4,TRUE) equals:
#VALUE! {Err.402} 2 missing or invalid cells found in Return column titled "Y2" (all cells must be numeric for Interpolate < 0 or > 2, and Missing_pts = FALSE).

Search for value = 0.75 in the lookup column "X2" and return a value from the return column "Y2"; use the default "exact match" (Interpolate = 0).

=TVLKP(0.75,$B$5:$E$10,"X2","Y2",,TRUE) equals:
#N/A {Err.312} Invalid Lookup_value = .75 (cannot find exact match in Lookup Column titled "X2").

Same as above, but using the "exact or next lower value" (Interpolate = 1) mode, which is similar to the Excel's built in VLOOKUP function.

=TVLKP(0.75,$B$5:$E$10,"X2","Y2",1,TRUE) equals Text

Search for value = 0.75 in the lookup column "X2" and return a value from the return column "Y2"; use a linear interpolation (Interpolate = 4). Also set Missing_pts = TRUE to allow processing of lookup column and return column containing missing (empty) and invalid (non-numeric) cells.

=TVLOOKUP(0.75,$B$5:$E$10,"X2","Y2",4,,,TRUE,TRUE) equals:
#N/A {Err.408} Invalid Lookup_value = 0.75 (out of bounds due to missing or invalid cells in Return column titled "Y2").

Same as above, but with Extrapolate = 2. This allows TVLOOKUP to extrapolate up to 2 units away from the last valid cell.

=TVLOOKUP(0.75,$B$5:$E$10,"X2","Y2",4,2,,TRUE,TRUE) equals 0.561785714

Same as above, but using different lookup column and return column ("Y1" and "X2", respectively). Note: if Interpolate <> 0, then the values in the lookup column must be constantly increasing or decreasing.

=TVLOOKUP(0.75,$B$5:$E$10,"Y1","X2",4,2,,TRUE,TRUE) equals:
#VALUE! {Err.310} Invalid Lookup Column titled "Y1" for Interpolate <> 0 (values not constantly increasing or decreasing: .38; .5; .45).

1 2 3 4 Next