Friday, 24 November 2017

Quick Excel Tip: VLOOKUP

Excel's VLOOKUP feature lets you turn it into a sort-of database.

If you use Excel to manage projects, invoices or other tasks where you enter the same set of contact details you can semi-automate the process using a handy function called VLOOKUP.

It's extremely handy but many descriptions I've read have been a little brain-bending so here's my cheat sheet.

Take the following breakdown of the function's different elements:

VLOOKUP ( a, b:c, d, FALSE)

Now let's explore each element:

a) Take this value...

b)
     } scan this range for the same value
c)

d) ... and report the related value in this column.

This means that Excel will take a value, such as a name, scan a range of cells looking for this same name and return related data, such as an address, age or pay rate.

EXAMPLE 1:

VLOOKUP(B3,$B$3:$G$100,2,FALSE)

Look at the value in B3 - this could be the exact name of a client: "Fred Smith"

Scan the range $B$3:$G$100

Find the matching client name ("Fred Smith") in this range and report the corresponding value in the second (2) column ("1 Redwood Avenue").

The 'FALSE' entry simply means that the match of B3 with the data in the specified range should be exact.

EXAMPLE 2:

VLOOKUP(B3,Clients!$B$3:$G$100,2,FALSE)

The same as above, but in this case the clients' details are stored neatly in a separate worksheet called Clients.