Microsoft Excel VLOOKUP function – Lookup for values ​​in an MS Excel database or table

Consider a simple spreadsheet in Microsoft Excel, containing a table of data in columns A through C as follows:

• Column A: the unique personnel number of an employee
• Column B – your name
• Column C – your salary

Assume there are 99 people in the table (ie, taking into account the column headers, the table ends at row 100). You want to find out someone’s salary, but all you have is their staff number. How can you do this using an Excel function?

The answer is to look up the person’s details in the lookup table (i.e. the table containing the person names and staff numbers) and return the value in the third column, making sure to specify that only an exact match it’s good enough.

To see how this works, suppose you enter the cell E1 a personal number (let’s say it’s 12345), and on a cell phone F1 you enter the following formula:

=VLOOKUP(E1,A1:C100,3,FALSE)

Four arguments are used here; Here’s what each one does (an argument is any bit of information you pass to a function in the brackets):

• E1 – this is the number of personnel that we want to find in the table
• A1:C100 – this is the table in which we are looking for the number of personnel. For a lookup table to work, what we are looking for (here the staff number) has to be in the first column of the lookup table.
• 3 – the column we are returning (here is the value in the third column of the table: i.e. the salary)
• FAKE – this means we must make 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 what to write FAKE 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 of a particular field in a database. The other use is when we want to search for a value in a range, but that is a topic for another article.