The LOOKUP Function

The function LOOK JP lookup value,lookup vector,result vector) has two syntax forms vector and array. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in another one-row or one-column range. The values in lookup vector must be sorted in ascending order. If LOOKUP can t find lookup value, it returns the largest value in lookup vector that is less than or equal to lookup value. 

The array form of LOOKUP is similar to VLOOKUP or HLOOKUP in that it automatically looks in the first column or row of an array, but is limited to returning a value from the same position in the last column or row. Go to Excel s On-line Help for more details. 

Excel Tip. Use the vector form of LOOKUP (instead of e.g., VLOOKUP) when you need to return a value from a column to the left of the column containing the lookup values, or when your lookup values are in a column and your return values are in a row. 

---

When you use VLOOKUP, you must always "look up" in the first column of the defined database and retrieve associated information from a column to the right in the same row you cannot VLOOKUP to the left, for example. In the table shown in Figure 9-2, you cannot use VLOOKUP to return the element name corresponding to symbol. If you want to perform a lookup to the left of, or above, the lookup value, you can either construct your own lookup-type function using the MATCH function (see later in this chapter) or use the LOOKUP function. 

Now we will generalize the lookup function so that we can look up the atomic mass of any element by simply typing its symbol into a cell. Click on cell 12, click in the formula bar, and edit the contents to read as follows ... 

The error condition N/A then appears in cell 12 because G2 is blank and thus contains no element symbol. Click on cell G2, and type Fe. The atomic mass of iron now appears in cell 12. Try typing several other element symbols in cell G2, and note the results. When you are satisfied that the LOOKUP function is working properly, click on cell 12, and copy the contents into cell 13, using the fill handle. Then type various element symbols in cell G3, and your worksheet should look something like the one shown in Figure 3-14. 

An alternative to fitting the data is to input the table of data into Excel and then use the Lookup function built into Excel to linearly interpolate between data points. 

A backup copy of the data files on the computer disk should be made onto a formatted 3.5 in. disk before using the files in any solvent-resin calculations. It is suggested that the Lotus. WKl files be copied to the appropriate spreadsheet directory on the computer hard drive for faster operation of the spreadsheets. The directions needed for proper operation of the spreadsheet are listed at the top of each spreadsheet. Any spreadsheet data and calculations that need be saved should be saved under a different file name. Any solvent or resin/polymer numbers that are typed into the appropriate spreadsheet cells must be listed in numerical order for the lookup function to work correctly. The data files can be used with Lotus 123 Version 5.0 for Windows, Microsoft Excel Version 5.0 for Windows, or any earlier version of the spreadsheet software. Anyone familiar with the use of macros in spreadsheets can undoubtedly automate the operation of some of the spreadsheets contained on the computer disk. 

Values in column B31..DS0 and C31..CS0 are essentially to be selected in pairs, when the ASTM condition number is input in column D7..D2S. This is done through the use of the LOOKUP function in columns E and F. For example, the formula stored in cell E7 is... 

The LOOKUP function instructs the program to look down columns in TABLEl for the value conesponding to D7, and pick up the fost ofket for E7 and the second olB set for F7. Note the use of the dollar sign in front of TABLEl. This is for convenience so that the formula can be copied into all cells from E7..E25 through the Copy command. Further, the V in front of LOOKUP function signifies that TABLEl is in the vertical format. In case it was in the horizontal format, then 6HLOOKUP function would be used instead to perform the same task, but with the difference of looking up rows. 

In Figure A2.2[a] the application of the powerful TABLE() and LOOKUP() functions of EXCEL as the formula in cell E 16, the uppermost top-left cell of the 121 x 121 [120-1-1 to include the lookjup values] selected area of the worksheet... 

You can use wildcard characters with the MATCH function. If your lookup values are text and match type is set to 0, lookup value can contain the asterisk ( ) and question mark ( ) wildcard characters. A question mark matches any single character, an asterisk matches any sequence of characters. 

You can use the MATCH and INDEX worksheet functions in a formula to perform linear interpolation in a table. The syntax of the MATCH function is MATCH lookup value,lookup array,match type). If match type = 1, MATCH returns the position of the largest array value that is less than or equal to... 

The ISERROR function was used with range lookup = 0 otherwise VLOOKUP returns N/A for all values of t for which there is no corresponding entry in DataTable. 

Can you describe what the following function represents (lambda (name) (lookup-in-table name (edr table) table- )) This function is the action to take when the name is not found in the first entry. 

When searching a database, if an isomeric query is used, only structures with the identical stereochemistry will be found using either a direct lookup or the matches function. If a nonchiral query is used, the direct lookup will find matching nonchiral structures, including canonical SMILES. When a nonchiral query is used in the matches function, structures of all chirality will be found. There is no one best method for dealing with a database containing many chiral molecules. It is important to carefully consider how to design and search such a database. 

It is possible to specify the isotope of any atom in a SMILES string. This is generally not necessary because the most common isotope is simply assumed. But if, for example, a database contains information about 13C, this can be readily encoded into the SMILES using [13C] instead of simply C. The [13C] atom is considered different from the normal C atom in a SMILES. A direct lookup using canonical SMILES will not locate isotopes of the same structure. A substructure search using the matches function will locate isotopes. This is because the match function uses SMARTS to specify the desired substructure. 

When the table contains unique canonical smiles in an indexed column cansmi, and the cansmiles function provides the proper canonical SMILES for phenol, this lookup is extremely fast. 

This cannot make use of the index on the column cansmi. Every row of the table must be examined to see if the matches function succeeds. This is a time-consuming process compared to a direct, indexed lookup. 

Function generators form another important class of synthesis units. These generators create the lookup tables for the oscillators and tables for sound transformation and control. Function generators fill data tables with values produced according to specific procedures or mathematical formulae, such as trigonometric functions and polynomials. The length of the function table is generally specified as a power of two, such as 2 = 512 or 2 ° = 1024 samples. 

The size of the recirculating lookup table is specified in Hertz and is usually set to be equal to the frequency value. The lookup table can be initialised by either a random sequence of samples or a user-defined function. As for the feedback method, the simple averaging method described above is the most commonly used. 

Cellsim s universe consists of a 512 x 512 array. The program itself is completely menu-driven and includes these features (1) 256-state computed-function rules, in addition to lookup-tables, on both the Sun and CM (2) creation of look-up tables using Langton s lambda parameter (see section 3.2) (3) the ability to save images in Sun raster format and (4) the ability to generate lookup tables with standalone C code. Cellsim can be obtained via anonymous ftp on the Internet to think.com. 

---