Using the vlookup function (exact)

Vlookup Tutorial For Beginners

The video above shows how to use Excel's vlookup to fill in data for you:
You will enter the student's score, and the spreadsheet will automatically fill in the corresponding letter grade for this score (for Example 70 will give the letter "C" letter), and the corresponding verbal description (in our example: "Below average").

How will vlookup know how to do it? Well, it will take the score you enter, and will look it up the table's first column, and retrieve the value that is written next to it.
In our first example, it will look for the value 70 in the table, and will retrieve the letter C which is in the 2nd column in the table.

You can download the spreadsheet used in this video by clicking the "Try it yourself" button above.

Building the function step by step:

1. Type the following code: =vlookup(
2. Type the address of the cell containing the value to look for in the table.
3. Type the range of the table to look inside, (or better: Name the table before starting with the function, and type now its name). Remember: don’t include the table’s heading row.
4. Type the column number from which you want to retrieve the result.
5. Type the word FALSE which means: “Please find me exactly the value of the cell mentioned in step 2. (Don’t round it down to the closest match)”.
6. Close the bracket and hit the [Enter] key.

Note: The function will always look for the value mentioned on step 2 only on the first column (the utmost left column) of the table.

Vlookup Examples:

Let’s assume you are using the following Excel worksheet.

(If you wish, you can download this worksheet here.)

Copying and replicating the function

If you plan on copying the vlookup function (either by “copy” and “paste” or by dragging it with the fill handle), make sure to set the table’s address with absolute reference (e.g. $A$2:$E$7). But it would be easier instead to simply name the table, as in the following example.

You can name the range A2:E7 by selecting it, and typing the name in the name box. (The name box is located on the top-left corner of the screen).
Let’s assume you named it studentsTable (spaces are not allowed, but you may use underscores).

Troubleshoot (advanced usage with more functions)

(You should be familiar with the IF function to understand this section)

If the vlookup doesn’t find a match, it will write the following code: #N/A. That's quite annoying.
You can overcome this code, and choose what do display in this case, by using the IF function with the ISNA() function.

The isna() function gives "true" if the vlookup inside it does'nt find any match. So we'll use it as the condition part of an IF function.

Look at the following example:

You can see a demonstration of using the IF with ISNA() function and read more about it over here.