Excel 2010 Bible

Chapter 14: Creating Formulas That Look Up Values

311

TABLE 14.1

Functions Used in Lookup Formulas

Function Description

CHOOSE Returns a specific value from a list of values supplied as arguments.

HLOOKUP Horizontal lookup. Searches for a value in the top row of a table and returns a value in the

same column from a row you specify in the table.

IF Returns one value if a condition you specify is TRUE, and returns another value if the condi-

tion is FALSE.

IFERROR* If the first argument returns an error, the second argument is evaluated and returned. If the first

argument does not return an error, then it is evaluated and returned.

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

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

function works like VLOOKUP but is restricted to returning a value from the last column of a

range.

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

OFFSET Returns a reference to a range that is a specified number of rows and columns from a cell or

range of cells.

VLOOKUP 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.

* Introduced in Excel 2007.

The examples in this chapter use the functions listed in Table 14.1.

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

panying figure 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”)

continued

Using the IF Function for Simple Lookups