Microsoft Excel VLOOKUP Function – Looking Up Values in an MS Excel Database or Table
Consider a simple spreadsheet in Microsoft Excel, which contains a table of data in columns A to C as follows:
Column A – the unique personnel number of an employee
Column B – their name
Column C – their salary
Assume that there are 99 people in the table (meaning that taking into account the column headings, the table ends on row 100). You want to find out someone’s salary, but all you have is their personnel number. How can you do this using an Excel function?
The answer is to lookup the person’s details in the lookup table (ie the table containing people’s names and personnel numbers), and return the value in the third column, making sure that you specify that only an exact match is good enough.
To see how this works, let’s suppose that you enter in cell E1 a personnel number (let’s say it’s 12345), and in cell F1 you enter the following formula:
=VLOOKUP(E1,A1:C100,3,FALSE)
There are four arguments used here; here’s what each does (an argument is any bit of information which you pass to a function in the round brackets):
E1 – this is the personnel number which we want to find in the table
A1:C100 – this is the table in which we’re looking up the personnel number. For a lookup table to work, the thing we’re looking up (here the personnel number) must be in the first column of the lookup table.
3 – the column we’re returning (here it’s the value in the third column in the table: ie the salary)
FALSE – this means that we must do an exact match. If you don’t specify this, then Excel may decide that it has found 12344, and this is close enough, with disastrous consequences. Many people decide that typing FALSE takes too long, and use 0 instead (which has the same effect).
That’s one use of an Excel VLOOKUP function: to return the value for a particular field in a database. The other use is when we want to lookup a value in a range, but that is a subject for another article.