Using the VLOOKUP Function in Microsoft Excel 2010

Even if you use Excel every day for work, it is still possible to find new functions seemingly hidden within the program that will make your life easier. The VLOOKUP function searches for a value you specify and “looks up” a corresponding value from another column within your excel workbook (or an external workbook).

As long as you’re aware of the structure of the formula, using the VLOOKUP function is simple (like looking up a phone number in a phone book). In this example tutorial using Microsoft Excel 2010, we’ll view sales referred by a partner then “look-up” the fixed commission fee per sale (CPA) to find a total payment amount owed.

Step 1

Begin the process by placing your cursor in the first blank cell in the column that you plan on using with the VLOOKUP function. When you insert the appropriate VLOOKUP function into the spreadsheet, it will then use this selected cell to retrieve that data that you're trying to find.

Step 2

In the blank cell you selected, you can manually type =VLOOKUP or open the “Insert Function” dialog box by either:

• clicking the ƒχ (function button)
• selecting “Insert Function” from the Formula Tab in your menu
• or pressing Shift+F3 as a shortcut

When the dialog box is opened you can search for VLOOKUP or select it from the drop menu.

Step 3

After the =VLOOKUP function is added, you will be prompted to define the function's arguments in a dialog box of the same name. To properly use VLOOKUP, you need to define three key arguments: (Lookup Value, Table Array, "col_index_num"). Note: It’s important that each argument is separated by a comma and closed on both ends with parenthesis.

Enter the value that you're starting with in the "Lookup Value" field in the window on screen. You can do this either by entering the information into the box using your keyboard or by clicking on the appropriate cell in your table. The latter option is easiest and most effective. In my example below, I clicked the B3 cell and it automatically populated the “Lookup Value”.

(To reiterate the format, ensure that your Lookup Value has a comma to close the argument before continuing to the next step).

Step 4

Next we’ll specify the range that you want the VLOOKUP function to use in the "Table Array" section. In order to select your Table Array, hover over the letter labeling the leftmost column associated with your Lookup Value from Step 3 on a separate table. Proceed to click, highlight and drag to the exact column with which you want to look up a corresponding value. Once your range of columns is selected, ensure that your Table Array has a comma appended at the end to close the argument before continuing to the next step. In my example below I’ve selected a range of 2 columns from a separate worksheet tab that lists out the Partner name and their associated fixed commission fee.

TIP: It's best practice to select from a range of columns in a separate worksheet for when you add and delete rows in the look-up tables, you won't accidentally tweak rows in any other table.

Step 5

Use the "col_index_num" box to specify the column number in your spreadsheet selected for your Table Array which contains the information you're trying to lookup. In this example I simply typed out “2” where the 2nd column contains the commission fee details I am looking to associate with the specific partner.

Step 6

While it’s not required, you may also use the "Range-Lookup" argument (entered after column number is added from the previous step) to specify how closely the results should match during the VLOOKUP search. If you wanted to find an exact match between the column and rows in your range, you would enter the term "False." I typically always use False option when pulling from an extensive list of varying data. After you enter the optional “Range-Lookup” you can close the VLOOKUP function with a final parenthesis and press the Enter button.

Once you’ve entered all of the aforementioned information, the VLOOKUP function should run automatically and display the result in the original cell selected in Step 1.

While the Commission Total formula displayed in column E is not related to the VLOOKUP function, the VLOOKUP function helped me figure out the commission automatically without having to manually lookup details and leaving room for human error.

Lastly, my favorite and most rewarding technique of all is dragging the VLOOKUP formula down the table to automatically populate the rest of the details in succeeding cells, as you’ll see below…

There you have it, a simple way to use the VLOOKUP function in your Excel spreadsheets.