Example: Effect of TOL on Lookup Functions

Functions > Vector and Matrix > Other Array 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.

<region id="ID0EAULFB" top="844.8" left="364.8" width="241.876666666667" height="137.2" xmlns="http://schemas.mathsoft.com/worksheet50">
  <math resultRef="62">
    <define xmlns="http://schemas.mathsoft.com/math50">
      <id label="Variable" xml:space="preserve">Y</id>
      <matrix rows="7" cols="3">
        <str xml:space="preserve">group</str>
        <str xml:space="preserve">a</str>
        <str xml:space="preserve">b</str>
        <str xml:space="preserve">c</str>
        <str xml:space="preserve">d</str>
        <str xml:space="preserve">e</str>
        <str xml:space="preserve">f</str>
        <str xml:space="preserve">number</str>
        <real>10</real>
        <real>20</real>
        <real>30</real>
        <real>40</real>
        <real>50</real>
        <real>60</real>
        <str xml:space="preserve">grade</str>
        <real>71</real>
        <real>85</real>
        <real>63</real>
        <real>90</real>
        <real>81</real>
        <real>69</real>
      </matrix>
    </define>
  </math>
</region>

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.