Master the Excel VLookup with this Simple Tutorial

Become an Office Hero with the Vlookup Function in Excel

excel vlookup tutoria Master the Excel VLookup with this Simple Tutorial

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.

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.

excel vlookup tutorial 15 Master the Excel VLookup with this Simple Tutorial

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.

excel vlookup tutorial 131 Master the Excel VLookup with this Simple Tutorial

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.

excel vlookup fx button Master the Excel VLookup with this Simple Tutorial

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

Click the OK button.

The Function Arguments wizard will open.

excel vlookup tutorial lookup value Master the Excel VLookup with this Simple Tutorial

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).

excel vlookup tutorial 10 Master the Excel VLookup with this Simple Tutorial

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.

excel vlookup tutorial 09 Master the Excel VLookup with this Simple Tutorial

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.

excel vlookup tutorial 08 Master the Excel VLookup with this Simple Tutorial

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.

excel vlookup tutorial 07 Master the Excel VLookup with this Simple Tutorial

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.

excel vlookup tutorial 101 Master the Excel VLookup with this Simple Tutorial

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.

excel vlookup tutorial 121 Master the Excel VLookup with this Simple Tutorial

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:

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.

excel vlookup tutorial 132 Master the Excel VLookup with this Simple Tutorial

Re-copy the formula down the list.

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

excel vlookup tutorial 14 Master the Excel VLookup with this Simple Tutorial

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:

[tubepress]

Comments

  1. By Erica

    Reply

  2. By Nigel

    Reply

  3. By Beverly

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>