Vlookup w/indirect


1. Source data

An analyst has a series of worksheets containing sample price and volume data for several stocks traded on the Australian Stock Exchange (ASX). The stock data for ANZ bank is shown in figure 1.


The download data from Yahoo Finance includes the series labels Date to Adj Close as shown in columns A to G of the ANZ.AX worksheet. The analyst has added the log return column, labeled Returns in column H with the Excel LN function used to create the vector \(r_t = log(P_{t} / P_{t-1})\)


Range blue = ANZ
Fig 1: Price and volume data for ANZ bank. The range shown in columns A to H, coloured blue is named ANZ, with worksheet ANZ.AX scope."]

A summary worksheet named Analysis has been created, and the analyst wants to use a master list of trading days to collate the return data for stock codes ANZ and CBA. The summary sheet is shown in figure 2.


lookup
Fig 2: The summary worksheet named Analysis. A master list of trading days is a column vector that starts at cell A14.

The best way to collate the individual return series is by using the VLOOKUP function with an exact match based on dates in the Date vector. This means, if the date from the master list cannot be found in the table_array, then VLOOKUP will return a #N/A error. The analyst should never simply copy and paste from the source worksheets as there is no certainty of one to one matching.


There are several ways to write the lookup formula. These are discussed next.


2. Writing the lookup function


2.1 VLOOKUP w/ direct reference


2.2 VLOOKUP w/ INDIRECT reference


Fig 1: Excel Web App #1: Analysis and Data worksheets. VLOOKUP function w/ INDIRECT on Analysis2.