Page 322 - Excel 2007 Bible

20_044039 ch15.qxp 11/21/06 11:07 AM Page 279
Creating Formulas That Look Up Values
When a Blank Is Not a Zero
he Excel lookup functions treat empty cells in the result range as zeros. The worksheet in the accompany-
Ting figure contains a two-column lookup table, and this formula looks up the name in cell B1 and returns
the corresponding amount:
=VLOOKUP(B1,D2:E8,2)
Note that the Amount cell for Charlie is blank, but the formula returns a 0.
If you need to distinguish zeros from blank cells, you must modify the lookup formula by adding an IF func- 15
tion to check whether the length of the returned value is 0. When the looked up value is blank, the length of
the return value is 0. In all other cases, the length of the returned value is non-zero. The following formula dis-
plays an empty string (a blank) whenever the length of the looked-up value is zero and the actual value when-
ever the length is anything but zero:
=IF(LEN(VLOOKUP(B1,D2:E8,2))=0,””,(VLOOKUP(B1,D2:E8,2)))
Alternatively, you can specifically check for an empty string, as in the following formula:
=IF(VLOOKUP(B1,D2:E8,2)=””,””,(VLOOKUP(B1,D2:E8,2)))

The INDEX function returns a cell from a range. The syntax for the INDEX function is
INDEX(array,row_num,column_num)
The INDEX function’s arguments are as follows:
n array: A range
n row_num: A row number within array
n col_num: A column number within array

NOTE If array contains only one row or column, the corresponding row_num or column_num argu-
NOTE
ment is optional.
Figure 15.5 shows a worksheet with dates, day names, and amounts in columns D, E, and F. When you
enter a date in cell B1, the following formula (in cell B2) searches the dates in column D and returns the
corresponding amount from column F. The formula in cell B2 is
=INDEX(F2:F21,MATCH(B1,D2:D21,0))

279