My favourite Excel function

I'm a self confessed Excel junkie.  I've tried other spreadsheets, but to me they just don't have the flavour of the real thing.



The Excel vlookup function is rather useful.  If you look above you can see it in action. 

In this example, we have a table of sales budget figures for each month (in green).  By changing the month number in cell D2 (in red) it gives the corresponding sales figure for the month in cell D6 (in blue).

To do this all that is required is the vlookup formula as follows:

=VLOOKUP(D2,F3:G14,2,false)

So what does that actually do?  The function looks up the value in D2 and looks for it in the first column in cells F3:G14.  It then returns the corresponding value in the second column.  Our our example the second column is the sales budget column.  Adding "false" just ensures that Excel looks for exact matches.

Our example is quite simple, but it does give a taster for what is possible with the vlookup command

Comments

[Franchise Whale]

Great stuff! Thanks for sharing, one fresh
idea and you can change the world, keep
up the great work.

[Andy Warren]

This can work really well when integrated into monthly cashflow forecasts and management reports. It can save a lot of cutting, pasting and re-keying.

HLOOKUP can do a similar thing on the horizontal.

[Philip Woodgate]

Hi Andy

The other function commands that individuals may find useful are the combination are Match and Index. They act in a similar, if slightly different way, to Vlookup.

[Jenny, Grafdom]

ohh Excel, what we do without you.

Thanks for the sharing this tip, it seems basic but when considered, its potential is vast. Will try experimenting now :)

[Philip Woodgate]

Thanks Jenny. Good luck with vlookup.