Have you ever found yourself entering the same data over and over again in multiple workbooks and wishing there was an easier way to cross reference your data? VLOOKUP is your solution!
VLOOKUP allows you to cross reference data across worksheets or even workbooks by “looking up” data in a column (hence Vertical Lookup aka VLOOKUP) and placing corresponding data in another cell.
For example, you may have one worksheet with billing rates and another worksheet used for tracking time. To determine the total amount that will be billed for the project you can use VLOOKUP to cross reference the rates from the first worksheet with the time incurred on the second worksheet by using a field they have in common. Excel will “lookup” the billing rate for Melissa Jones in the Billing Rates table (see below) and place it in the Rate cell for her time entry in the Time Tracking table (see below).
Understanding the Function Dialog Box
The most complicated part of any function is knowing what goes where and why. Once you understand how to fill out the Function Argument dialog box you will be ready to us VLOOKUP. The table below gives an explanation of each argument and an example. One trick to always remember is if the argument is bolded then it is required and if not it is an optional argument.
|Lookup_Value||The data being cross referenced. Note: this must be the first column of the “lookup” table.||Melissa Jones which is C2 in the Time Tracking sheet|
|Table_array||The “Lookup” table. This tells Excel where to look both for the “lookup value” and “result”. This can be in the same workbook or in a separate workbook.||‘Billable Rates’!A1:C25|
|Col_index_num||This is the column number from the table array (see above) that contains the value you want to be inserted into the “results” table. Each column is numbered sequentially from left to right beginning with 1.||3|
|Range_lookup||True if you want it to find the closest match. False if you want an exact match.||False because we only want to find an exact match on names. True would allow “Jim Smith” to be substituted for “Jim Smitherson” and would return incorrect billing rates.|
When working with functions the most important step is starting the correction location. You always want to start your function where you want to your description or value to be returned to.
- In the example above we want the result to be returned to the Time Tracking table in the Rate field which is cell E2. So we will begin by clicking in this cell.
- Navigate to the Formulas ribbon, in the Function Library group choose the Lookup & Reference dropdown and choose VLOOKUP.
- The function argument dialog box opens for VLOOKUP.
a) Lookup_Value: enter the cell name we want to “lookup”, in this case it is C2.
b) Table_array: choose the selector icon which will collapse the dialog box. Select the entire “Lookup” table.
c) Column_index_num: enter the column number from the lookup table that contains the data you wish to return. In this example, the column number is 3.
d) Range_lookup: enter true for approximate matches or false for exact matches. In this case, we will enter False to return only results that correspond to an exact match on names. True would allow “Jim Smith” to be substituted for “Jim Smitherson” and would return incorrect billing rates.
e) Click OK.
Note: you can use fill the handle to copy the formula down the rest of the table.
VLOOKUP is one of the more powerful functions that is frequently overlooked as a tool because people don’t know how to us it. Hopefully, the article demystified the function of VLOOKUP. Should you ever need to look horizontally in a row rather than vertically in a column, there is a similar function called HLOOKUP.