Microsoft Office Tutorials and References

In Depth Information

Returns one value if a condition you specify is TRUE, and returns another value

if the condition is FALSE.

IF

If the first argument returns an error, the second argument is evaluated and re-

turned. If the first argument does not return an error, then it is evaluated and re-

turned.

IFERROR*

INDEX

Returns a value (or the reference to a value) from within a table or range.

Returns a value either from a one-row or one-column range. Another form of the

LOOKUP function works like VLOOKUP (or HLOOKUP) but is restricted to re-

turning a value from the last column (or row) of a range.

LOOKUP

Returns the relative position of an item in a range that matches a specified

value.

MATCH

Returns a reference to a range that is a specified number of rows and columns

from a cell or range of cells.

OFFSET

Vertical lookup. Searches for a value in the first column of a table and returns a

value in the same row from a column you specify in the table.

VLOOKUP

* Introduced in Excel 2007.

The examples in this chapter use the functions listed in Table 8-1.

Using the IF function for simple lookups

The IF function is very versatile and is often suitable for simple decision-making problems. The accompanying fig-

ure shows a worksheet with student grades in column B. Formulas in column C use the IF function to return text:

either Pass (a score of 65 or higher) or Fail (a score below 65). For example, the formula in cell C2 is

=IF(B2>=65,”Pass”,”Fail”)

You can “nest” IF functions to provide even more decision-making ability. This formula, for example, returns one of

four strings: Excellent, Very Good, Fair, or Poor.