Jan 7 2015

Microsoft Excel 2013 VLookup – Part One


This is the first of several upcoming blog posts we are writing as a followup to the original post John did on the Microsoft VLookup Function way back in 2007.


VLookUp Function – Familiarizing the Function

Aside from Microsoft Word, one of the most commonly used computer programs nowadays is Microsoft Excel. It is used by so many people, both on the Mac and on the PC, to input information, to perform calculations on that data as well as creating charts, reports, and Pivot Tables. In order to efficiently use the program it is essential to know the functions that it can carry out. With the so many functions available, currently over 400, one of the most important is the VLOOKUP. Many feel that this is also one of the functions that many users find difficult to understand. But in reality, once you take the time to learn it, it is a snap to use and you too will wonder how you could have got your work done without it.  The other option, learn Index and Match which is even more confusing and intimidating.   And what it is able to do for the user is so profound that it is one of the most used functions in accounting and finance.  That is why it is vital to know its basics to become familiar with it easily.


See the One-Hour Video on the Microsoft Excel 2013 VLookup















The Basics – VLookup Function Definition

The VLookup function is a function that searches for the value located in the table, data range, or array. It does so by looking at the contents of the cells of the left most columns in the data set, and it returns the value that is in the same row, but a different column, in accordance to the index_number, zero or more columns to the right. To use it look at the table, data range or array that contains the data you are looking for, by first looking at the first column for the value to be identified. Once the value or the next closest value is found, based on your selection in the 4th parameter, look to the right, a specific number of columns and return any value that is found there. That is it.  Super simple.


Syntax for VLookup function

The syntax that is used in utilizing the function is “=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).

The bracket on the syntax means that the argument is optional. So, it is not necessary to use it, but it does need to be understood, otherwise the results may not be accurate. More will come on that later.


Understanding the Syntax VLookup Function

Lookup_value – this is the value you want to lookup in the data range’s first column. It can be a value, a formula or a cell reference. It is very important that the column has no repeated values. If this is so, then the VLookup will use the value that was first detected and will ignore the other(s). The values are either entered in logical values, number or text. Even if text is present, the case is not sensitive.

Table_array – the array could be a range of data. Once it consists of two or more columns of information, it is identified as range. The range or array can be determined through reference range. The headers of the information can be included once they do not obstruct the searching of lookup_value.

Col_index_num – this is the number of the column in the range that has the value desired to be returned. A great example of this is the two columns of information situated in columns E and F. Column E identified as the lookup column, has the values to search for and Column F has the value to return. Hence, Column E is determined as column 1 and hence the other hand, column F is column 2. Once the VLookup function searches the values in column E, and it finds the value at row 5, Excel returns the value in row 5 and column 2. Always keep in mind that the system of the number of column used is related to the array of data and not the location of the column in the worksheet. Once the column started at G then it is identified as the column 1 in VLookup. If ever the proper column is not entered in the syntax there is an error that will be returned.

Range_lookup – the function can search for approximate and exact match within the first column of the information being searched. Better use the term TRUE if searching for the approximate match, and the term FALSE for the exact match.


Things to Keep in Mind in VLookUp Function

In searching for the value of the text in the first column of the data, make sure that the information located within the first column does not contain leading spaces, inconsistent utilization of straight quotation marks, nonprinting characters and trailing spaces. With these, the function might give unexpected or incorrect value.

In looking for date or number values, make sure that the information located in the first column are not entered in text values. If it is in text values, then the function will give incorrect results.

Once the range_lookup case is FALSE and the lookup_value is text, wildcard characters should be used such as asterisk and question mark. One question mark is equals to any single character and one asterisk could match whatever character sequence.

These are the important things to know about VLookUp functions. It’s important to be familiar first with all the function of Microsoft Excel, for you to be able to use it efficiently.


Quality Resources when it comes to the Microsoft Excel VLookUp Function

Previous blog post on ExcelHints.Com: http://excelhints.com/2007/04/20/vlookup-in-excel

One of the Best Resources on a Forum Type Site: http://www.ozgrid.com/Excel/excel-vlookup-formula.htm

One of the Best Sites for Excel Help: http://www.contextures.com/xlFunctions02.html

A One Hour Video on the VLookup Function, by Zack Barresse, Microsoft Excel MVP: http://www.excelandaccess.com/excel-and-access-videos.htm

Our YouTube Video on the VLookup Function:  http://youtu.be/5wqgS-_tygc

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 


Comments are closed.

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks