How to Do a VLOOKUP in Excel

Master the Excel VLookup with this Simple Tutorial

www.VitaminCM.com

By Christopher Masiello

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.

www.VitaminCM.com

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.

www.VitaminCM.com

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.

www.VitaminCM.com

Slide Title

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.

www.VitaminCM.com

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

Click the OK button.www.VitaminCM.com

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

www.VitaminCM.com

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.

www.VitaminCM.com

Slide Title

Click in the Range Lookup field.

There are two values that you can use in the Range Lookup field: TRUE orFALSE. True will find the “closest” value in the range and False will only find EXACT matches.

Typically I want an exact match.

www.VitaminCM.com

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

www.VitaminCM.com

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.

www.VitaminCM.com

Additional Resources

This slide deck accompanies the full article on VitaminCM.com. See the full tutorial at http://vitamincm.com/excel-vlookup-software-tutorial/ The article shows you how to use Microsoft Excel's VLOOKUP function to compare values in two lists.

Master the Excel VLookup with this Simple Tutorial www.VitaminCM.com

How to Do a VLOOKUP in Excel

Master the Excel VLookup with this Simple Tutorial

www.VitaminCM.com

By Christopher Masiello