Excel’s VLOOKUP function is undoubtedly one of the most popular Excel functions. It can readily interpret and report values in large data arrays and unlike a PivotTable, which needs to be manually updated to reflect source data changes; VLOOKUP will seamlessly update to reflect such changes. The practical application of VLOOKUP in various industries is as clear, as the ubiquitous use of financial and business spreadsheets across different functions of a business.

The following Excel spreadsheet  is used to illustrate the following basics of the VLOOKUP function.

Strategize Financial Modelling – VLOOKUP example

 

Components of a VLOOKUP

 

Components of a VLOOKUP table

lookup_value The referenced in the first column of the data array. It can be a text string, reference or an integer.

Table_array The array or table of data, text, or values through which data is to be retrieved. By default, the array must be presented in ascending order.

Col_index_num The column number in Table_array, which references the matching value of the lookup_value. 

Range_lookup Locates the closest match in the lookup_value column (sorted in ascending order) if applying “TRUE”; otherwise the use of “FALSE” will locate an exact match.

 

[youtube]http://www.youtube.com/watch?v=JsVG25E_nxA[/youtube]

 

VLOOKUP in Financial Modelling

This function is a fantastic tool to use. The key takeaways to consider when using the VLOOKUP function are:

  • The lookup_value will always reference the first value – even if the lookup_value has multiple references,
  • Be mindful to apply absolute cell referencing to the Table_array or create a defined range name,
  • The Col_index_num is absolute – any additions or deletions (be careful!) to the columns will cause erroneous VLOOKUP values, and
  • Range_lookup should generally apply the “FALSE” parameter by default; unless you’re seeking to reference the closest match, then use the “TRUE” parameter.