Principles of Financial Modelling

CHAPTER 25Lookup and Reference Functions

INTRODUCTION

This chapter discusses Lookup and Reference functions (which, for simplicity, are referred to in the subsequent text only as lookup functions). A good knowledge of these is one of the most important capabilities required to construct intermediate and advanced models.

The chapter starts with examples that relate to basic referencing processes (most of which are either essentially self-explanatory, or have already been covered earlier in the text):

• FORMULATEXT, which shows (as text) the formula in a cell.
• TRANSPOSE, which transposes an array.
• COLUMN (ROW), which returns the column (row) number of a cell or range.
• COLUMNS (ROWS), which finds the number of columns (rows) of a cell or range.
• ADDRESS, which provides a cell reference as text.
• AREAS, which shows the number of areas (separate non-contiguous ranges) in a reference.

The majority of the rest of this chapter is devoted to examples which use the other core functions in additional contexts, including combining matching and referencing processes, and the creation of dynamic ranges and flexible data structures:

• INDEX looks up the value in a specified row and column of a contiguous range (as a one- or two-dimensional matrix). The function also exists in a reference form, where it returns a reference to specified cells rather than to the value of a cell.
• CHOOSE uses one of a set of values according to an indexation number. It is especially useful (compared to other lookup ...