VLOOKUP formula in Excel with an example

A VLOOKUP explained

Image you’re looking at your work rota for January 2020.

You have the days of the month listed in a single row across the top of the page, (1 through to 31) and you have the names of every employee along the left-most column of that table.

To find out if you’re working on the 15th of January, you go down the first column and find your name, and identify which row you’re on.

You then find the column for the 15th by looking at the top row, and you go down the same number of rows where your name is on.

The point where the row and your column meet, will tell you what shift you are scheduled to work that day.

This is also how a VLOOKUP works.


VLOOKUP Example

If we look at the above example, we can out if Darcey is working on 15-Jan.

The VLOOKUP formula will look like this

=VLOOKUP(    “Darcey”    , $A:$G,    5,    0)

The first input is “Darcey”, this tells Excel that it is Darcey we are interested in looking for.

The next input is the table range that we want to search. This table exists in columns A to G. You can also use a more defined range, such as $A$1:$G$10. The $ signs are important, because they stop the range of the table changing as you copy the formula.

Now that you have provided the first and second inputs, Excel knows you are looking for “Darcey” in the first column of the range $A:$G; this is critical feature of a VLOOKUP – it always searches in the first column of the range provided.

The next number, 5, says return the data from the 5th column.

Finally, the “0” (zero) at the very end tells example to only return exact matches, and not to return approximate results.

Therefore, if “Darcey” exists in the first column of the range $A:$G, then take that row, look at column 5, and return the result. In this example, it will return “Full”.

VLOOKUP Result

In the above, we found Darcey on row 4, and we told the formula to return the data from column 5, therefore it returns “Full”

If we changed the 5 to a 4, we would return the data from the 4th column, which would be nothing (in this instance, Excel would return a 0 [zero]).

Similarly, if we changed the input “Darcey” to say “Tyra”, and kept the formula the same (to look in the 5th column), it would return a 0 (to indicate a blank).