Master the Excel VLookup with this Simple Tutorial

Become an Office Hero with the Vlookup Function in Excel

Over the years I have been the Excel tips guy in my office. There are so many amazing things that Excel can do, but the one thing I get asked more than every other feature combined is: “How do I do a Vlookup”. Mastery of the Vlookup seems to be the one Excel trick that people use to separate experts from novices. Well, guess what? It’s really not hard at all.

Excel VLOOKUP Video Tutorial

See how to do all of the steps listed here in this video tutorial:

What Exactly is a Vlookup?

The Vlookup function in Excel is used to see if a value in one cell (or list of cells) is in another group of cells. (“V” is for vertical – you can use th Hlookup function to check horizontally too) Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. Take a look at the data below. Column A has a list of people who have already paid their bill. Column B has a list of customers who received services. We want to find out which customers have paid and which still owe payments. This is a typical Vlookup scenario: compare two lists and identify the differences.

Naming a Range in Excel

Before you actually create your Vlookup formula, you should create a “Named Range”. Excel can take a range of cells and assign a name to it that can be referenced in functions and formulas throughout the workbook.

Select the range of cells that contain the values that you want to check against.

TIP: If you have a list of values that may be added to, select a range that is bigger than your existing list. This way, when the list grows, the new values will still be included in your named range. After you select your range of cells, you are ready to give it a name. Right click on the highlighted cells and select Name a Range from the menu.

The New Name menu will open. Type a name in the Name field. You can modify the actual range of cells (now or later) in the Refers to field.

Click the OK button.

Now you can refer to this range (Paid) anywhere in this workbook.

Creating the Vlookup Formula

Now that we have a range of data to check against, we are ready to build our formula.

Click to the right of the first value that you want to check.

Click the formula builder button (fx) to search for the Vlookup function.

Select the Lookup & Reference category and scroll down to the VLOOKUP function.

Click the OK button.

The Function Arguments wizard will open.

The Function Arguments wizard lists what the overall function does below the fields. When you click in one of the fields an explanation of what goes into that field is displayed. There are four fields to populate:

• Lookup Value
• Table Array
• Column Index
• Number Range Lookup

Click in the Lookup Value field and enter the cell for the value that you want to check against the range (B2 – Allan).

Click in the Table Array field and enter the name of the range that we want to search for the Lookup Value.

Type “paid” in the Table Array field to reference the named range of values. Notice how the values in the named range appear to the right of the Table Array field.

Click in the Column Index Number field.

The range that we named contained all of the values in one column (A). If we had a range of data that resided in more than one consecutive column, we could specify which column’s value that we wanted to return to the formula cell. Since we only have one column, type “1” in the field.

Click in the Range Lookup field.

There are two values that you can use in the Range Lookup field: TRUE or FALSE. True will find the “closest” value in the range and False will only find EXACT matches. Typically I want an exact match.

Type “FALSE” in the field and click the OK button.

If the value is in the range the value from column 1 will be displayed in our cell.

Extending the formula down the rest of the list

Just copy and paste the formula you built (C2) and paste it down the rest of the list.

All of the remaining values will be checked against the range.

If a value is not found “#NA” will be displayed. In our case, we will know that those people did not pay yet.

Since we made our named range a little bigger that the existing data, we can add more data to the empty cells in the range and have them rechecked.

The example below shows that as long as we type into the range that we created it will be included in the formulas that reference it.

Using a Multi Column Named Range to Extend your Vlookup

We used a single column range in the example above. Multi Column named ranges allow you to do some more interesting things with your Vlookups. I added an extra column to the spreadsheet that lists the amount that each person paid.

Then, I edited the range (Formulas tab> Name Manager> Select Paid> Edit button> Change range from “A29” to B29”)

Wanna See More Great Excel Tips and Tricks?

These articles have even more Excel Tutorials and quick tips:

• Linking Cells in Multiple Spreadsheets
• Create a Dropdown List in Excel
• Amazing Free Excel Plugin
• Applying Conditional Formatting to Excel Data

This included the Amount column as the second column in the range. You can add several columns if you like. Now, when I find someone that already paid, I don’t want to return their name (I already know their name) I want to know how much they actually paid. (Column 2)

Reopen the Function Arguments wizard and click in the Column Index Number field.

Type “2” in the field and click the OK button.

Re-copy the formula down the list.

Now, you’ll see who paid, how much they paid, and who still owes money.

That’s it – you can do the Vlookup with the rest of the Excel gurus out there.

Excel VLOOKUP Video Tutorial

See how to do all of the steps listed above in this video tutorial: