Balanced Scorecards and Operational Dashboards with Microsoft Excel, 2nd Edition

Chapter 18

Retrieving Data from Lists and Tables of Data

Information is a source of learning. But unless it is organized, processed, and available to the right people in a format for decision-making, it is a burden, not a benefit.

—William Pollard

Historian

Although charts are what people usually look at first in Balanced Scorecards and dashboards, it is tables and lists that are the source of the information. They contain the data from which Excel builds charts, displays tables, fills menus, and builds printed reports. They are also what managers and executives refer to when they want more detail. Being able to manage your lists and tables dynamically will save you a significant amount of time, give your dashboard users more capabilities, and reduce chances for error.

Other techniques you will learn in this chapter include sorting lists using a function rather than a command. This enables you to retrieve or import data from any data source, sorting it multiple ways according to your user needs.

More Powerful Than VLOOKUP: INDEX and MATCH

One of the first powerful Excel features people learn as they move from novice to intermediate level is the use of the vertical and horizontal lookup functions, VLOOKUP and HLOOKUP. The VLOOKUP function searches the first column in a list for a term and then returns the value in the row of the item found, but in a column you specify. HLOOKUP accomplishes the same function on horizontal lists. These functions are usually used to retrieve items, such ...