13. Excel Tips - VLOOKUP

Nick's picture

VLOOKUP is an extremely useful function and you should know it like the back of your hand.
In a few words, it allows you to look up a value in a table, and return another value on the same row.

In my example, you've bought yourself a massive Buy To Let portfolio, and are wondering when you can retire. On typing in the new prices, you realise this might not be any time soon.

Now, you'd like to be able to type in the purchase number, and return the new value.. so here's how to do it:
Data
VLOOKUP example sheet

If you want to know for example the 5th property you bought, you type 5 in cell C3.

What if "Purchase Number"

What if "Purchase Number" typed is greater than available number of stocks? I think the VLOOKUP function can be improved by IF function.

Cell C3 can be revised as follows:
=IF(C2Cell C4 can be revised as follows:
=IF(C2