Lookup Functions and Data Tables

HLOOKUP

=HLOOKUP(look up value, table range, row number) This function searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. *Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows.

MATCH Function

=MATCH(lookup_value, lookup_array, [match_type]) The Match function returns the relative position (number) of an item in an array that matches specified lookup value. *Particularly powerful when combined with other lookup functions. For the third argument, "Match Type", you typically want an exact match and will therefore enter 0 for that argument of the function.

VLOOKUP

=VLOOKUP(lookup value, table range, column number) This function searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table or array. *Use VLOOKUP when your comparison values are located in a column to the left of a table of data, and you want to look across (and to the right) a specified number of columns. **Table Range is just the data table you're using so just highlight with shift arrow keys

Common Errors with Lookup Functions

All of these lookup functions on their own are not very helpful because they can get disturbed when you add additional rows or columns. When combined with the Match function, they come much more useful.

Indirect function combined with Concatenation

Allows you for example to find the dynamic sum of the total EBITDAs from say 2014-2018. You can change those dates and have a corresponding change in the sum total of EBITDA.

CHOOSE

Another lookup function but is even more static and less dynamic than index.

Data Tables

Data tables allow us to examine a piece of output data- such as a company's EPS- and how it is impacted by changes in input variables such as revenues and gross margin assumptions. Often used for sensitivity analysis (EPS' sensitivity to changes in gross profit margin) and is widely used by analysts to illustrate a range of potential outcomes.

Horizontal Data Tables

Exact same thing as vertical data tables except the various assumptions (revenue growth rate) run horizontally across columns and your original output variable is off to the left of the first column and one row below. You simply fill in the information in the data table drop-down menu opposite as you would for a vertical data table.

Using Match Function with other Lookup Functions

For Offset function with match, make sure that you add a -1 outside the parentheses of each match function within the offset function because the counting of rows and columns for offset is different than with the other functions.

H/VLOOKUP Cont.

In both of these functions, there is an optional fourth argument called "range lookup". If "range lookup" is omitted, or "TRUE", an approximate match to the lookup value is returned. If the "range lookup" is "FALSE", an exact match is returned. **When the lookup value is text or numbers in non-ascending order, the FALSE range lookup is often preferred.

OFFSET

More flexible lookup function that does not require you to highlight the entire data table. +OFFSET(reference point, rows, columns) For reference point, just select the top left hand corner of the data table. For rows and columns, you are typing in the number of rows BELOW that reference point (so if the table is four rows long, you would type 3 if you wanted the last one because you take into account the row of the reference point) and the number of columns to the RIGHT of the reference point (same concept with the counting).

2-Sided Data Table

Most useful type. Same as vertical data table but allows for two inputs instead of one.

Vertical Data Tables

Place the various assumptions (Revenue growth rate for example) in a column going down on the left hand side and then one column to the right and one row above, have the core model EPS assumption for example with the spaces below that for the new outputs based upon the various revenue growth rate assumptions. Next step is to run the data table. Hit Alt,d,t. For Row Input Cell: Nothing is needed for vertical data tables Column input cells: Highlight the entire range (including the output variable, which is the original EPS from the core model) and hit OK when done. Hit F9 afterwards because we have excel set up for automatic calculations except for data tables. **When building a data table, it must always be in the same worksheet as the input variables.

INDEX

Similar lookup function but more static in the sense that you need to count the rows and columns manually to enter.

Indirect and Address Function

Using an Indirect and address function together can allow for the dynamic search for tables such as a list of countries and their rank by GDP. First just make a simple indirect function analog and you will copy and paste an address function into it. Make an address function analog and then change dynamically either the row, column or both within the address function using a match function. Copy that entire address and match function and then paste it within the previous indirect function, deleting the analog components of the original indirect function. **If you are creating a drop down menu on a different worksheet but pulling information from a previous worksheet, you can use the same exact combination of address and match with indirect functions to do so. At the end of your address function, add ,'s until you get to the last argument, "sheet name" and just write whichever sheetname it is in quotation marks.