Excel’s lookup and referencing formulas

Excel’s lookup and referencing functions help get data from a table (e.g. a table containing key inputs) into the right place in your financial model. Certain Excel functions, or combinations of functions, can really help modellers get data where they want it. Here we look at Excel’s Vlookup function. This Excel function always gets lots of questions on a financial modelling course, so we know it’s popular!

Excel’s Vlookup function: margin grid example

You can download an example of Excel’s Vlookup function at work here. You can see the Vlookup function at work at line 11. Play with the values at line 10. That’s Vlookup at work, helping get data in the right place. In this example we are imagining that we have a pricing or margin grid (lines 4-7). Pricing and margins change depending on a key financial ratio or banking covenant. What we’re achieving at line 11 is applying the correct margin given the ratio at line 10.

Syntax for the Vlookup function

How is Excel’s Vlookup function constructed? Click on cell B11, and then click on the “fx”/ insert function button (“Shift” “F3”) in the Excel formula bar. You’ll get a little clue as to the main components of the Vlookup.

Image showing how to use Excel's Vlookup function in financial modelling

Excel needs to know what table of data to look in

To start, let’s look at the second element of the Vlookup function, the “table array”. That second element tells Excel which table of data we’re interested in. Excel is going to be hunting for values inside that table. In our example the data we’re pointing Excel to is cells A4:B7.

Vlookup – what next?

Next Excel is going to search the first column of the table to try to work out which row it is interested in. That’s the “lookup value” – the first part of the function. In our case, at cell B11, the lookup value is cell B10, containing the value of 5.2. So what Excel is going to do is look for the value of 5.2 in the table A4:B7. Because 5.0 is the nearest possible match (in the last row of the table), at this point Excel ‘knows’ the important data is in the first row of the table A4:B7.

Getting a match on the column

Next Excel counts across a number of columns in the table A4:B7. That’s the third part of the Vlookup function (the “column index number”). In this case the data we’re interested in is in the 2nd column of the table.

What’s Excel’s Vlookup function really doing?

In our example Excel is finding a match on the row (the value of 5.0 or the last row). It’s getting a match on the column (the 2nd column in our example) and supplying us with the piece of data where the first row and the fourth column intersect (2.00%).

What’s the “range lookup” thing all about?

The last part of the Vlookup function at cell B11 contains room to enter (i) nothing (ii) “TRUE” or (iii) “FALSE”. If you enter “FALSE” Excel will look for an exact match in the table of data. If it can’t find an exact match, it will return an error.

In this pricing/ margin grid example, we want to Excel to provide us with a result even when we have a value at line 10 that doesn’t exactly match what’s in the grid e.g. 5.2. So what we’ve done in this example is enter nothing (which, to Excel, is the same as having entered “TRUE”: options (i) and (ii) above amount to the same thing as far as Excel is concerned).

Our top modelling tip on Vlookup

Don’t get into the habit of just leaving the range lookup blank. Always have a think about whether you want an exact or an approximate match. We’d suggest in many cases, where you are looking to pull exact data out of a big table, you’d want to enter “FALSE”. We’d suggest you get into the habit of usually entering “FALSE” on the end of your Vlookups – that’s our modelling tip in this one.

Vlookup vs. Hlookup

If you can get your head around Vlookups you will be able to understand Hlookups. Press “Shift” “F3” while you’re in a blank cell and type in Hlookup at the top, and then click on “Go” or press “Enter”.

Image showing Excel's Hlookup function

Vlookup looks first for a match on the row and then finds a match on the column. Hlookup works the other way around – matching the column first and the row second. Because of the way people tend to set out data, most of the time it’s Vlookup you’ll use.

Excel’s Vlookup function: conclusion

Vlookups are tricky to use to start, but can be very powerful when you’re working with a big set of data. You have to be able to master them to survive your modelling career! There are lots of other options for finding data in big tables, but providing you set out your Vlookups correctly, you should be able to solve all your data retrieval problems using Vlookup. Sometimes you’ll find people around you solving the problem a different way (there’s always five different ways of solving the same problem in Excel!) so soon we’ll be covering some of your other alternatives.

Coming up in the Excel financial modelling course extract

Later we look at the alternative of using the Choose function to get data where you want it in an Excel financial model. In the next lesson though we look at the power and usefulness of Excel’s goal seek function.

About this online financial modelling course material

You’re looking at a free online financial modelling course extract covering Excel functions.



4 comments so far:

...join the conversation...

Your email address will not be published. Required fields are marked *

  1. The article is very informative. Thank you
    What formular do you use to look up Price and size of an item by keying in the name

  2. Good!

  3. Good stuff!