Part 9: Managing Databases and Lists in C#.NET

Print QR Code in C#.NET Part 9: Managing Databases and Lists

28

The Lookup Wizard is an add-in that walks you through the process of finding a value in a vertical table that s in a column to the right of some specified value in the table. In other words, it provides an alternative to building a formula with the VLOOKUP function. If you ve installed the Lookup Wizard add-in, you can run it by choosing Tools, Wizard, Lookup. If you don t find this command, you need to install the wizard. You can do that by choosing Tools, Add-ins, and selecting Lookup Wizard. Unlike the VLOOKUP function, however, the Lookup Wizard requires an exact match in the leftmost column of the lookup table. That s because it constructs its formula using the MATCH and INDEX functions, instead of the VLOOKUP or HLOOKUP function. We ll look at MATCH and INDEX next.

Like VLOOKUP and HLOOKUP, the MATCH function searches a range that you specify for a particular value that you specify. Unlike these other functions, however, MATCH returns the position within the range at which it finds the lookup_value argument. For example, if the item you re looking for is the fifth item in the range, MATCH returns the number 5 not the item itself. The syntax for MATCH is MATCH(lookup_value,lookup_array,[match-type]). The lookup_value argument can be any value (text or numeric) or a reference to a cell containing any value. The lookup_array argument can be a column, a row, or an array specified within the formula. The optional match-type argument is 1 by default and can be any of the following: Match-type

28

MATCH looks for the largest value smaller than lookup_value. Lookup_array must be sorted in ascending order. MATCH looks for exact matches only and returns #N/ A if there is no exact match. Lookup_array need not be sorted. MATCH looks for the smallest value greater than lookup_value. Lookup_array must be sorted in descending order.

The MATCH function is often used to provide an argument to the INDEX function. The INDEX function returns the item at a specified row and column position within a range. The syntax is INDEX(array,row_number,column_number) or INDEX(reference,row_number, column_number,area_number).

Managing Information in Lists In the first syntax, array can be a range or an array specified within the formula. In the second syntax, reference can be a single range or multiple noncontiguous ranges; if multiple ranges are specified, area_number indicates which of the specified ranges INDEX should search. If array or reference is a single column, the column_number argument can be omitted. If array or reference is a single row, the row_number argument can be omitted. If the combination of column_number and row_number points to a cell or value lying outside array or range, the function returns #REF! In Figure 28-39, the formula at D2 uses MATCH and INDEX to look up the value for Eva Corets s salary. The formula reads

=INDEX(A6:G371,MATCH(C2,A6:A371),MATCH(D1,A6:G6))

The array argument for INDEX is A6:G371, the address of our staff list. The row_number argument is supplied by MATCH(C2,A6:A371), which returns 57, because, in the current sort order of the list, Corets (the value of C2) is at the 57th position within A6:A371. The column_number argument is supplied by MATCH(D1,A6:G6), which returns 6, because Salary (the value of D1) is at the sixth position within A6:G6. The INDEX function therefore returns 79000, the value lying at the intersection of the 57th row and sixth column of A6:A371.

Figure 28-39. We ve used INDEX in conjunction with MATCH to return the value at a particular intersection within the staff list.

If the row_number argument for INDEX is 0, INDEX returns an array consisting of all the values in the column specified by column_number. Similarly, if column_number is 0, INDEX returns an array of the values in the row specified by row_number. You could therefore fetch