Using TriLookup

2. Do I Need TriLookup?

You will benefit from using TriLookup if your requirements go beyond the limited capabilities of the built-in Excel’s lookup functions (MATCH, LOOKUP, VLOOKUP and HLOOKUP) and/or if you wish to use Excel to fit polynomial curves through sets of X-Y points. For example, you may have encountered some of the following problems:

Excel’s HLOOKUP and VLOOKUP built-in functions require that you specify the sequential number of the table row or column containing the data. Therefore, if you later modify your lookup table by inserting or deleting rows or columns, you must remember to go back and update the row/column numbers in the formulas using HLOOKUP and VLOOKUP functions. Otherwise, you may get the #VALUE! errors, or incorrect results by inadvertently retrieving data from unintended rows or columns.
If you have an error in a cell formula using the built-in lookup functions, you will probably receive an Excel error code, such as #VALUE! or #N/A. Since you get no explanation about what caused the error, you can spend a lot of time trying to debug it.
Excel’s built-in lookup functions cannot retrieve an interpolated value when the lookup value falls in-between the values that are given in the lookup table. At best, you can get an "approximate match", described in Excel’s help topic for VLOOKUP as "... approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned". For example, if the lookup column contains values ... 1, 10, 100, 1000 ... and your lookup value is 99, then the "approximate match" will return the table value for 10.
If you have a 2D lookup table in which the return values depend on two lookup variables, X and Y, you have to use a convoluted formula to retrieve a value from the table, for example:

=INDEX(LookupTable,MATCH(Y_value,LeftColumn,1),MATCH(X_value,TopRow,1))

Notice that in order to accomplish this simple task, you need to enter three lookup functions and separately reference three ranges of cells (one for the whole lookup table, one for the leftmost column and one for the topmost row). However, if there is no exact match for both X and Y lookup values, the best you can get is an "approximate match", as described in the paragraph above.

Suppose that you have a 3D lookup table (made up of a series of 2D lookup tables) in which the return values depend on three lookup variables, X, Y and Z. While it is still possible to use Excel’s lookup and reference functions to retrieve a value from a 3D table, it requires that you use a separate cell with a formula similar to the one in the paragraph above to perform X-Y lookup in each component 2D table, and then use an additional cell formula to perform Z lookup. Again, if there is no exact match for X, Y and Z lookup values, you can only get an "approximate match", as described above.
If you want to fit a polynomial curve through a set of X-Y points you can, in theory, find its coefficients it by using Excel’s built-in TREND and LINEST functions. In practice, however, you will probably give up before you get this method to work. Alternatively, you can insert an X-Y chart using the table data as a source, then add a polynomial trendline, display its equation on chart, cut and paste the trendline equation into a cell, and after some editing obtain a working formula for the polynomial curve. In addition to the number of steps involved, the problem with this method is that the maximum polynomial order that you can use is 6.

If you have encountered any of the difficulties described above, then you should find TriLookup functions helpful in your work.

Previous 1 2 3 4 5 6 Next