Slide19
Date: 23/08/2016 | By: Les Gutteridge

Excel’s VLOOKUP function is one of the most useful functions in Excel, but it can also be difficult to understand.

 

BizSmart Select Member Les Gutteridge, of Microsoft Office training company 101 SystemWorks, explains in his recent webinar exactly what this function is for and how it works,

click here to access the recording.
 

SelectMember_LessGutteridge_s

Excel’s VLOOKUP function saves businesses time

 

He also expands on a range of other useful functions to enable you to speed up your systems and processes, saving you time and resources in your business.

 

What is VLOOKUP?

 

VLOOKUP is a database function. Put simply, this means it works with database tables or lists in an Excel spreadsheet. An example of how you might use this function is to pull details through from a product list into a customer invoice.

Slide6

If you have a list of products, where the first column (Column A) is the product code or ID number, then you can pull details through from this list onto an invoice. The details you might want to include are the item description and the price, but you could also add in details like the colour and size of the item.

 

So once you have set up Excel’s VLOOKUP function, every time you enter details into your invoice template you can simply add a product code and all of the other details will be retrieved automatically.

 

If you want to save time in this way, listen to Les’s webinar 

 

Using Excel functions can save hours of time each week, freeing up you and your staff to focus on other important tasks.

 

In the webinar, Les also discusses:

NAMES

These are great in formulas and have other benefits too

COUNTIF

 

Count up how many cells in a range that pass a certain test. E.g. how many sales reps exceeded a sales target in a month?Slide7

SUMIF

Similar to COUNTIF but this time the function adds up only the cells where a certain test is fulfilled. E.g. what was the sales total for all the reps that exceeded the sales target?

Slide8

COUNTIFS & SUMIFS

COUNTIF & SUMIF can only deal with a single criterion whereas COUNTIFS & SUMIFS are designed to Count or Sum cells that match multiple criteria

Slide8

MATCH

Often used to find the Row Number in a search column where the look up value lives

Slide11

INDEX

Gets the Value from a particular Row of range

INDEX MATCH together

A good alternative to VLOOKUP… 
MATCH finds the row number in one column, matching the value, and INDEX retrieves the value in that row but from another column

ARRAY Formulas

If you have a column of quantities and a column of prices then an array formula can give you the grand total of quantity x price in one go. To do this a special key press needs to be known.

SUMPRODUCT

The purpose of the SUMPRODUCT function is to multiply, then sum, up to 30 arrays at once without the need of that vital keyboard shortcut.

 

BizSmart Select Member Les Gutteridge, of Microsoft Office training company 101 SystemWorks, explains in his recent webinar exactly what this function is for and how it works,

click here to access the recording.
 

BizSmart aims to help SME and micro-business owners scale their businesses and create value through sound practical business support. We aim to give you insight and clarity and fire up your determination to succeed. You can access blogs like this and more besides through our free SmartRoom service here.

Bizz_CTA_form