Choose and Vlookup are built-in functions. You could get by without them, but in certain situations they make your spreadsheets easier to create, modify and understand. The feature they facilitate particularly well are reference tables.
Choose is the simplest of the two functions. It is self-contained in that the reference values are contained within the function. Here is the syntax:
=CHOOSE(<choice>,<option 1>,<option 2>, ..., <option n>)
<choice> is an integer between 1and n. If <choice> is 1, <option 1> is returned by the function. If <choice> is 2, the value of <option 2> is returned, etc. Choose is a useful function because it can be used to implement a table of values. <choice> can be a relative cell reference, and the same Choose function can be used to calculate a whole column of data. (For example, you can write the function once for the first row and then Fill Down to complete the entries for the remaining rows.) Here is an example:
Vlookup is a bit more complex, but it enables you to write more readable and maintainable spreadsheets. Here is the syntax:
=VLOOKUP(<lookup value>, <table>, <column>)
Vlookup is different from Choose because the reference values are stored in the spreadsheet rather than with the function itself. In the function prototype above, <table> specifies the location in the spreadsheet of the table of values. The Vlookup function causes the first column of <table> to be searched for a value greater or equal to <lookup value>. When this value is found the contents of the cell in the same row and <column> columns to the right is returned. Here is an example:
Vlookup works in conjunction with a reference table you define in the spreadsheet. There are certain requirements on this table that must be met for Vlookup to work properly. First, the values in the first column must be sorted in ascending order. Second, the first value in the first column of <range> must be less than or equal to all possible lookup values.
 |
 |
|
Copyright 1996 by the Curators of the University of Missouri |