Functions > Vector and Matrix > Other Array Functions > Example: Effect of TOL on Lookup Functions
Example: Effect of TOL on Lookup Functions
Show how the value of TOL can affect results returned by the hlookup, lookup, vhlookup, vlookup and match functions.
1. Define an input matrix.
2. Use the hlookup function to find the element at the point of intersection of the column that contains z and row r.
3. Use the hlookup function and the not modifier to return all the elements in row r except the one at the intersection of the column that contains z and row r.
4. Use the lookup function to match z in X then return the values in the same position in Y.
a. Find an exact match.
Matrix X contains a 50 at positions (5,0) and (1,2).
b. Use the less than-modifier lt to find a match that is less than a specified value.
Matrix X contains three values that are less than 4 at positions (1,0), (2,0) and (3,0).
c. Use the range modifier to find a match that falls within a range.
Matrix X contains three values that fall between 50-70 at positions (5,0), (6,0) and (1,2).
5. Use the vhlookup function to match z1 and z2 in the first column and row of A, respectively, then return the value at the intersection of the matching row and column.
a. Find an exact match.
Column 0 of A contains one value that matches z1 and row 0 contains one value that matches z2.
Column 0 of A contains two values that match z1.
b. Use the near modifier to find a match that is near the specified z1.
Column 0 of A contains two values, 5 and 6, that are near z1.
c. Repeat finding the above match but with a newly specified tolerance.
Increasing the tolerance lowers the degree of precision and a new element, 4, meets the conditions for the specified modifier and tolerance.
6. Restore the value of TOL.
7. Use the vlookup function to match z in the first column of A then return the value at the intersection of the matching row and the column specified by c.
a. Find an exact match.
b. Use the near modifier to find a match that is near the specified value of z.
Column 0 of A contains three values that are near 1.5 in rows 1, 2, and 3.
c. Repeat finding the above match but with a newly specified tolerance.
8. Restore the value of TOL.
9. Use the match function to match z1 in A then return the index of each matching value.
a. Find an exact match.
b. Use the near modifier to find a match that meets the specified value of z2
c. Repeat finding the above match but with a newly specified tolerance.
d. Restore the value of TOL.
e. Use the less-than modifier lt to find a match that is less than the specified value of z3
f. Repeat finding the above match but with a newly specified tolerance.
Increasing the tolerance while using the lt modifier causes one element to no longer meet the conditions for the specified modifier and tolerance.
g. Restore the value of TOL.
h. Use the range modifier to find a match that is within the range specified by z4.
i. Repeat finding the above match but with a newly specified tolerance.
10. Restore the value of TOL.