Microsoft Excel 2007: Functions and Data Analyses

Part 2: PivotTables
INFORMATION TECHNOLOGY SERVICES
California State University, Los Angeles
Version 1.1 Spring 2009

Contents
Introduction ................................................................................................................................... 2 

Scenario.......................................................................................................................................... 2 

Creating a PivotTable ................................................................................................................... 2 

Creating a PivotTable Report...................................................................................................... 2 
Adding PivotTable Report Fields ........................................................................................... 4 
Selecting a Page Field Item..................................................................................................... 5 

Revising a PivotTable Report ...................................................................................................... 5 

Refreshing a PivotTable Report .................................................................................................. 5 
Changing the Summary Function ............................................................................................... 6 
Adding New Fields to a PivotTable Report ............................................................................ 6 
Changing the Order of Data Fields ......................................................................................... 7 
Working with Dates .................................................................................................................... 7 
Showing and Hiding Detail..................................................................................................... 8 
Hiding/Displaying PivotTable Fields ..................................................................................... 8 
Applying AutoFormats ............................................................................................................... 9 

Creating a PivotChart Report ................................................................................................... 10 

Creating a PivotChart Report .................................................................................................... 10 
Changing a Chart Type ......................................................................................................... 11 
Dragging Fields from the PivotTable Field List ................................................................... 11 
Formatting a PivotChart............................................................................................................ 12 
Displaying Data Labels ......................................................................................................... 13 

Saving and Publishing a PivotTable in HTML Format .......................................................... 14 

Saving and Publishing a PivotTable ......................................................................................... 14 
Opening a PivotTable in the Web Browser .............................................................................. 15 

Lookup Functions ....................................................................................................................... 16 

LOOKUP .................................................................................................................................. 16 
VLOOKUP ............................................................................................................................... 18 
HLOOKUP ............................................................................................................................... 19 

For additional Microsoft Office handouts, visit http://www.calstatela.edu/its/docs/office.php

Introduction
In the second part of Microsoft Excel 2007: Functions and Data Analyses, PivotTables and
Lookup functions will be discussed. PivotTables enable the users to summarize and manipulate
numerous amounts of data in a small amount of space. Lookup functions essentially return a
value from a table in a range by looking up another value. This handout illustrates how to create
and revise a PivotTable report and a PivotChart Report, publish a PivotTable in HTML format,
and use the LOOKUP, VLOOKUP, and HLOOKUP functions.

Scenario
Many companies and organizations use Excel to deal with numeric data and analyze it from a
different perspective. A bank report is one of the examples which show how a dataset is analyzed
based on multiple conditions. For instance, it is used to show the account balance, the account
average balance, the number of accounts, and the open date of the accounts in each branch. Also,
it seeks to compare the data between branches. In addition, the use of different kinds of graphs
provided in Excel enhances the analysis of the data as well as its appearance. Therefore, this
handout will demonstrate how the user can customize a bank report appropriate to the purpose,
by employing the PivotTable or PivotChart wizard. Furthermore, it will help the user to
understand how to perform numerous calculations with ease in the PivotTable and PivotChart.
The dataset of Part two includes several data fields such as bank branch, account type, customer
type, and open date, which demonstrate the relevant data for a bank report. The bank branches
include Central, Northwest, and Westside. There are three types of bank accounts which include
the CD (Certificate of Deposit), Checking, and Saving Accounts. The dataset categorizes the
customers into four types, such as Personal, Personal VIP, Corporate, and Corporate VIP
depending on their current balance, average balance, and other factors affecting their credit for
the past five years. In this dataset, the average balance and other factors will not be displayed for
the users to simplify creating a PivotTable report. Also, the dataset shows only the open dates of
accounts from 2001 to 2005, but the date items can be grouped by months, quarters, and years in
the PivotTable and PivotChart Reports.

Creating a PivotTable
A PivotTable, also called PivotTable Report, is a summary table which uses two-dimensional
data to create a three-dimensional table based on multiple conditions that have intersecting
points. PivotTable can summarize large amounts of information in a small amount of space. The
advantage of the PivotTable is its flexibility to view the details that make up the total number
and its ease in performing numerous calculations without typing a formula. A PivotTable Report
is created using the PivotChart Wizard, which walks the user through the step-by-step process.

CREATING A PIVOTTABLE REPORT

To open the data file:
1. Select the Start menu ► Microsoft Excel.
2. Click the Office Button and then Open . The Open dialog box appears.
3. In the Open dialog box, select the drive where the data file is stored in the Look in:
list box. Double-click the file named “PivotTable.xlsx”.

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 2

To create a PivotTable:
1. Place the cursor anywhere in the range of the database.
2. From the Insert tab, select PivotTable under the Tables group. The Create PivotTable
dialog box opens (see Figure 1).

Figure 1 – PivotTable & Chart Wizard (Step One of Three)

3. The range of data is automatically given as; $A$1:$E$200. It resulted from the position
of the cursor in the worksheet before starting the Create PivotTable dialog box.
Otherwise, click on the edit box and select a range of data to use from $A$1:$E$200.
4. Select New worksheet under Choose where you want the PivotTable report to be
placed.
5. Click the OK button. “Sheet1” is automatically created (see Figure 2).

Figure 2 – Drag Data Items

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 3

Adding PivotTable Report Fields
When the fields from the selected data appear on the right side of the screen, each field needs to
be added into the PivotTable layout.

The following describes the four areas that are available for adding a field (see Figure 3):
1. Report Filter: Creates a drop-down menu above the table to analyze a specific item
from the field, such as a selected branch.
2. Row Labels: Applies a vertical format to the table, summarizing the data from top
down. The row drop area lists each item in the field down the left side of the PivotTable.
3. Column Labels: Applies a horizontal format to the table, summarizing the data from
left to right. The column drop area lists each item in the field across the top of the
PivotTable.
4. Values: The data drop area is the summary of the numbers. This area adds, counts, or
creates other analytical functions against the data dropped here.

Figure 3 – Field Placement Areas

To add fields into the PivotTable layout under the PivotTable Field List dialog box:
1. Click the Branch field. Drag and drop it into the Report Filter drop area.
2. Click the Account Type field. Drag and drop it into the Row Labels drop area.
3. Click the Customer Type field. Drag and drop it into the Column drop area.
4. Click the Balance field. Drag and drop it into the Values drop area (see Figure 4).

Figure 4 – PivotTable Layout

5. Double-click on the worksheet tab “Sheet1” and rename it to “PivotTable”.

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 4

Selecting a Page Field Item
When each field is dropped in the drop area, a user can customize a summary report by selecting
one of the items in the page field drop-down box.

To select a page field item:

1. When the field data is dropped in
the Report Filter drop area, the
PivotTable Report displays a
summary of all the data because
(All) is selected by default (see
Figure 5).
2. To apply report filtering, click the
drop-down box next to Branch and
select Northwest (see Figure 6).
3. Notice the PivotTable Report Figure 5 – Selecting a Page Field Item
displays the summary data of the
Northwest branch only
(see Figure 7).

Figure 6 – Changing a Page Field Item Figure 7 – Northwest Branch Displayed

Revising a PivotTable Report

Once a PivotTable or PivotChart is created, the user can restructure the PivotTable or PivotChart
by dragging and dropping fields, by using the options on the menus, or by using the PivotTable
toolbar. Formatting options can also be used to change the look or structure of the PivotTable.

REFRESHING A PIVOTTABLE REPORT

After a PivotTable is created, if some data in the original worksheet has changed, the PivotTable
must be refreshed to automatically update the contents of the table.

To refresh a PivotTable Report:

1. Click on worksheet “Data.”
2. Click cell D3, as a Checking product opened in the Northwest branch.
3. Change the value of cell D3 to 1,000,000.
4. Click worksheet “PivotTable.”
5. Click on the Options tab under the PivotTable Tools contextual tabs.
6. Click on the button in the Data group (see Figure 8). Notice that the PivotTable
automatically refreshes the data.

Figure 8 – Refresh Button

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 5

CHANGING THE SUMMARY FUNCTION
By default, a PivotTable uses the SUM function to summarize the data, but the user can change
the summary method of a PivotTable or PivotChart data by using different summary functions.

To change the summary function:

1. In the PivotTable, right-click cell A3 in the data area and
select Value Field Settings…. The Value Field Settings
dialog box appears (see Figure 9).
2. In the Summarize value field by section, click the
COUNT function to count the number of occurrences of
each balance type.
3. Click the OK button.
4. Notice that the PivotTable displays the count of balance
(see Figure 10).
Figure 9 – Count Summary Function

NOTE: Another way of changing the summary function is by right-clicking cell A3 and then
choosing Value Field Settings… from the drop-down menu.

Figure 10 – Count Summary of Balance Function

Adding New Fields to a PivotTable Report

New Fields can be added to the PivotTable to view other aspects of the bank balances.

To add a new field:

1. Open the “pivot_rev” worksheet.
2. From the PivotTable Field List dialog box, click the Open Date field and drag it to the
left of the Account type column in the PivotTable (see Figure 11 and Figure 12).

Figure 11 – Adding a New Field Figure 12 – Open Date Field Added to PivotTable

NOTE: Different field items can only be places in certain areas. See Table 1 for the list and
description of the icons that appear when dragging a field over the PivotTable.

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 6

Table 1 – PivotTable Field List Icons
Icon Description of Icon
Can only be used in the Row, Column, and Range field areas
Can only be used in the Data Range of the PivotTable
Can be used in any field area in the PivotTable

Changing the Order of Data Fields

To change the order of data fields:
1. Place the cursor in cell A4 and
right-click.
2. Point to Move on the shortcut menu.
3. Select the Move “Open Date” to
Right option (see Figure 13).
4. Notice that the Open Date field has
moved to the right of the Account Figure 13 – Changing the Order of Data Fields
Type field.

NOTE: As an alternative way, point the cursor on a data field cell. The pointer becomes a four-
headed arrow. Drag the data field to the desired position.

WORKING WITH DATES

PivotTable enables numeric items, dates and times, and selected items to be grouped by a desired
range. This section explains grouping by dates, but grouping other items can be applied in the
same manner.
To group dates:
1. Place the mouse cursor in cell A4.
2. Right-click and select the Group…option
(see Figure 14).
3. The Grouping dialog box appears. By
default, the two dates, Starting at: and
Ending at:, are automatically selected based
on the field data (see Figure 15).
4. In the By list box, select Months, Quarters,
and Years to group the dates in the data field
area by month, quarter, and year (see Figure
16). Notice the data fields are now grouped
by years, quarters, and months in separate Figure 14 – Grouping by Dates
columns.

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 7

 Figure 15 – Grouping Dialog Box Figure 16 – Grouped by Year

Showing and Hiding Detail

In the PivotTable, users can show or hide details of the fields.

To hide details of the Year field:

1. Click the field’s
heading, cell A4.
2. Right-click and go to
Expand/Collapse.
3. Select the Collapse Figure 17 – Hiding Details
option (see Figure 17).

To show hidden details of the

field:
1. Click the field’s
heading, cell A4.
2. Right-click and go to
Expand/Collapse. Figure 18 – Show Hidden Details
3. Select the Expand option
(see Figure 18).

Hiding/Displaying PivotTable Fields

PivotTables enable a particular data field to be hidden and redisplayed.

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 8

To hide the Open Date field in the
PivotTable report:
1. Find the PivotTable Field List
dialog box. If not found, click the
Field List button in the
Show/Hide group on the Options
tab of the PivotTable Tools
contextual tabs on the Ribbon.
2. In the PivotTable Field List,
uncheck the box for the Open
Date field (see Figure 19). Notice
that the Open Date field is hidden
in the PivotTable report. Figure 19 – Hiding PivotTable Fields

To redisplay the hidden field in the

PivotTable report, simply recheck the
checkbox of that hidden field in the
PivotTable Field List dialog box (see
Figure 20).

NOTE: As an alternative way, select cell A4

and click on the Expand Entire Field
button in the Active Field group in the
Options tab of PivotTable Tools contextual
tabs on the Ribbon. The Show Detail dialog
box appears (see Figure 21). Then, double Figure 20 – Redisplaying the Hidden Field
click the Open Date field.

NOTE: Keep in mind that a cell must be

selected in the PivotTable in order for
PivotTable Tools to appear on the Ribbon.

Figure 21 – Show Detail Dialog Box

APPLYING AUTOFORMATS
In Excel, it is possible to manipulate and format a PivotTable by changing the font, point size,
colors, etc. This section shows how Excel can automatically apply a preset style to a new
PivotTable.

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 9

To apply a format to a
PivotTable:
1. Place the cursor
anywhere in the
PivotTable
report.
2. Click the Design
tab under
PivotTable Tools
contextual tabs
on the Ribbon.
3. Select one of the
available styles
from the
PivotTable
Styles group (see
Figure 22). Figure 22 – PivotTable Styles

Creating a PivotChart Report

A PivotChart Report provides interactive analysis of data, like a PivotTable Report. The display
view of the data can easily be changed to see different level of details, and the organization of
the chart layout can be changed by dragging fields and by showing or hiding items in fields.
There are several different types of data sources that can be used, such as the list or table taken
from an Excel list or range, an external database, or another PivotTable Report.

CREATING A PIVOTCHART REPORT

To create a PivotChart Report using an existing PivotTable:
1. Open the file “PivotChart.xlsx” from the student data directory.
2. Select the worksheet “PTable”.
3. Select any cell in the PivotTable area.
4. PivotTable Tools contextual tabs appear. Select the Options tab and then click on the
PivotChart button under the Tools group.
5. Select the chart type preferred. Click on the Column tab at the left and then double-click
Stacked Column (first row, second from left). A PivotChart is created on the same
worksheet (see Figure 23).

Figure 23 – PivotChart Report with PivotChart Filter Pane

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 10

 6. To change the chart location, click on the PivotChart to select it and click on the Design
tab on the Ribbon. Select the Move Chart button under Location group. Click on
the New Sheet option button. The PivotChart will be moved to “Chart1” sheet.

Changing a Chart Type

To change the chart type:
1. Click anywhere on the PivotChart
to select it. The PivotChart Tools
contextual tabs appear.
2. Click on the Design tab. Click the
Change Chart Type button
under the Type group. The Change
Chart Type dialog box opens.
3. Select Clustered Bar (fourth
row, first column) (see Figure 24).
Figure 24 – Changing Chart Type
4. Click the OK button.

Dragging Fields from the PivotTable Field List

A PivotChart offers the power of PivotTables and normal charts combined in one interactive
medium. As the fields are dragged to new locations on the chart, Excel will pivot the chart to
correspond to the new field location.

To drag fields from the PivotTable field list:

1. Click on the PivotChart to display the PivotChart Tools contextual tabs.

2. Click on the Analyze tab. Click on the Field list button under Show/Hide group.
3. Drag the Account Type field to the Axis Fields (Categories) list box under the Years field
(see Figure 25).

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 11

 Figure 25 – Dragging Fields to PivotChart Report

FORMATTING A PIVOTCHART
Formatting changes the appearance of the chart. The formatting options available depend on the
selected object. For example, if the chart area is selected, Excel allows the patterns, fonts, and
chart area properties to be changed. The user can also use the buttons on the Formatting toolbar
to format text, add values, data points, and data series, as well as fill colors and patterns.

To format a chart title using the formatting button on the Chart toolbar:
1. Open the file “PivotChart2.xlsx” from the student data drive.
2. Click on the worksheet “Chart1” to begin formatting.
3. Click on the chart title once to select it. The PivotChart Tools contextual tabs appear.
Click on the Format tab.
4. Click on the Chart Area drop-down arrow to select an object to
format. Click on the Format Selection button (see Figure 26). The Format Chart Title
dialog box appears.

Figure 26 – Format Tab

5. Select the Fill tab. Change fill color and type of fill as desired (see Figure 27).

Figure 27 – Format Title (Fill)

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 12

 6. There are also the Border Color, Border Styles, Shadow, and 3-D Format tabs to change
the format as desired. Change border style, line color, line weight, and filled area color
(see Figure 28).

Figure 28 – Format Title (Border Color and Shadow)

7. Select the Alignment tab to change text alignment and orientation degree (see Figure 29).

Figure 29 – Format Title (Alignment)

8. Click on the Home tab to change the font style and font size (see Figure 30).

Figure 30 - Font Group

Displaying Data Labels

Without labels for data, it can be difficult to determine what each item refers to. Excel allows
data labels to be displayed in the PivotChart (see Figure 32).

To display data labels:

1. Select the Layout tab and select the Data Labels button under the Labels group.
2. Select More Data Label Options. The Format Data Labels dialog box opens. Under the
Label Options tab, select the Percentage check box, and uncheck the Value and Show
Leader Lines check boxes. Under the Label Position section, select the Outside End
option (see Figure 31).
Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 13
 Figure 32 – Displaying Data Labels

Figure 31 – Applying Data Labels

Saving and Publishing a PivotTable in HTML Format

Excel can save a PivotTable as a regular table in an HTML document, allowing other people to
view the table using their Web browser without having to open up an Excel file. Excel also offers
the option of republishing the Web page each and every time the Excel file containing the
PivotTable is saved. The table in the Web page is read-only and displays the last contents
displayed in the PivotTable when it was last saved in Excel.

SAVING AND PUBLISHING A PIVOTTABLE

To save a PivotTable as a Web
page:
1. Open the data file
“pivottable_html.xlsx”
from the student data
drive.
2. Click on the worksheet
“PivotTable.”
3. Select the Office button,
► Save as Web Page.
4. Specify the location of the
file to save (e.g.,
Desktop).
5. Select the
radio button (see Figure Figure 33 – Saving a PivotTable
33).
Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 14
 NOTE: If a range is selected (PivotTable) before using the Save as Web Page command in the File
menu, the Selection option will reflect the selected range instead of the entire sheet.

6. In the File name: field, type [PivotTable].

7. Click on the Change Title… button.
8. Type [Bank Report] in the Change Title text box.
9. Click the OK button.
10. Click on the Publish button. This opens up the Publish as Web Page dialog box.
11. In the Publish as Web Page dialog box, select the sheet from the Choose list. Then select
the entire sheet or just the PivotTable range. For example, under Item to publish, select
Items on PivotTable and then PivotTable (PivotTable2($A$1:$E$8)) (see Figure 34).
12. Check the AutoRepublish every time this workbook is saved check box to republish the
Web page whenever the workbook in Excel is saved.
13. Click on the Publish button.

Figure 34 – Publish as a Web Page

OPENING A PIVOTTABLE IN THE WEB BROWSER

Once a PivotTable is saved as a Web page, users can view the PivotTable in a Web browser,
such as Internet Explorer or Netscape Navigator.

To open a PivotTable in a Web browser:

1. Launch Internet Explorer by clicking on the icon.
2. In the File menu, select the Open… option to find the location of the PivotTable file. The
Open dialog box appears.
3. Click on the Browse… button (see Figure 35).

Figure 35 – Open the PivotTable in Web Browser

4. Click Desktop in the Look in: drop-down list and select the file, “PivotTable”.

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 15

 5. Click the Open button.
6. In the Open dialog box, click the OK button. The PivotTable appears in the Web browser
(see Figure 36).

Figure 36 – Viewing the PivotTable in Web Browser

Lookup Functions
A lookup function essentially returns a value from a table in a range by looking up another value.
A common telephone directory provides a good analogy. To find a person’s telephone number,
the name must first be located, and then the corresponding number can be retrieved. This is
essentially how a lookup function works.

This section discusses various techniques that can be used to look up a value in a table. Excel has
three functions (LOOKUP, VLOOKUP, and HLOOKUP) designed for this task.

LOOKUP
LOOKUP has two different syntaxes. One is used to return a value from a single column or row
and the other is used to return a value from a specified table array. Either syntax can be used.

LOOKUP(lookup_value, lookup_vector, result_vector)

lookup_value: The value searched for in the lookup_vector. It can be a number, text, logical
value, name, or reference.

lookup_vector: The single column or row of values to look up and return (if result_vector is
not specified), which can be text, numbers, or logical values.

NOTE: The values in a lookup_vector must be sorted in ascending order; otherwise, LOOKUP
may not return the correct value.

[result_vector]: (optional) The single column or row of values to return if specified. The range
size must be the same as the size of the lookup_vector.

LOOKUP will compare the lookup_value with each value in the lookup_vector and stop at the
position of the largest value that is smaller than the lookup_value. It will then return the value
stored in the same position it stopped on from the result_vector. This is the reason why the
size of the result_vector must be identical to the size of the lookup_vector.

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 16

For example, to assign a letter grade according to a numeric value:
1. Open “Lookup Functions.xlsx” if necessary, and select the “Lookup” worksheet. The
range G4:G8 contains some grades and the range H4:H8 contains the corresponding letter
grade (see Figure 38).
2. Type the formula [=LOOKUP(A4, $G$4:$G$8, $H$4:$H$8)] into cell B4.

NOTE: Absolute references, $G$4:$G$8, $H$4:$H$8, are needed since the formula needs to be
copied. Another way to do this is by assigning a name for the range and using that name in the
argument.

3. Copy the formula into range B5:B13 (see Figure 37).

Figure 38 – Reference Table

Figure 37 – Result of LOOKUP Function

LOOKUP(lookup_value, array)
Similar to the above syntax, the only difference is that the lookup_vector and result_vector are
now combined together into a table array entry. This is the easiest syntax to use.

Consider the previous example once more, this time by entering a table array instead.
1. Type the formula [=LOOKUP(D4, $G$4:$H$8)] into cell E4.
2. Copy the formula into range E5:E13. The result is exactly the same as the previous
example, but only two arguments are required in this syntax instead of three arguments
(see Figure 39).

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 17

 Figure 39 - Result of LOOKUP Function Using an Array

VLOOKUP
VLOOKUP searches for a value in the leftmost column of a table, and then returns a value in
the same row from a specified column in the table. Table values must be sorted by the left
column.

VLOOKUP(lookup_value, table_array, col_index_num)

lookup_value: The value to be searched for in the first column of the specified table. The
lookup_value can be a value or a reference. If the lookup_value is smaller than
the smallest value in the first column of table_array, VLOOKUP returns the
#N/A error message.
table_array: Two or more columns of data. The values in the first column of table_array are
the values searched by lookup_value. These values can be text, numbers, or
logical values. Uppercase and lowercase text are considered equivalent.
col_index: The column number in table_array from which the matching value must be
returned. A col_index of 1 returns the value in the first column in table_array;
a col_index of 2 returns the value in the second column in table_array, and so
on.

For example, to find the phone number and e-mail of a student within a range of data:
1. Select the “VLookup” worksheet. A table is listed in the range A3:C11 with the columns
“NAME”, “PHONE”, and “EMAIL”.
2. Type [Mary] into cell E6. This will be the value to be searched in the table.
3. Type the formula [=VLOOKUP(E6, A3:C11, 2)] into cell F6. The result is the phone
number corresponding to “Mary” (see Figure 40).
4. Type the formula [=VLOOKUP(E6, A3:C11, 3)] into cell G6. The result is the e-mail
address corresponding to “Mary”.
5. Type a different value into cell E6 and observe the VLOOKUP values change
accordingly.

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 18

 Figure 40 – Result of VLOOKUP Function

HLOOKUP
HLOOKUP works similar to VLOOKUP. It searches for a value in the topmost row of a table
and returns values in the same column and from a specified row in the table. The table values
must be sorted by the top row.

HLOOKUP(lookup_value, table_array, row_index_num)

lookup_value: The value to be found in the first row of the table. The lookup_value can be a
value, a reference, or a text string.
table_array: A table of information in which data is looked up.
row_index: The row number in table_array from which the matching value will be
returned. A row_index_num of 1 returns the first row value in table_array, a
row_index_num of 2 returns the second row value in table_array, and so on.

For example, to find the phone number and e-mail of a student within a range of data:
1. Select the “HLookup” worksheet. A table is listed in the range B1:J3 with the rows
“NAME”, “PHONE”, and “EMAIL”.
2. Type [Mary] into cell C7. This will be the value to be searched in the table.
3. Type the formula [=HLOOKUP(C7, B1:J3, 2)] into cell D7. The result is the phone
number corresponding to “Mary” (see Figure 41).
4. Type the formula [=HLOOKUP(C7, B1:J3, 3)] into cell F7. The result is the e-mail
address corresponding to “Mary”.
5. Type a different value into cell C7 and observe the HLOOKUP values change
accordingly.

Figure 41 – Result of HLOOKUP Function

Microsoft Excel 2007: Functions and Data Analyses, Part 2 - 19