VLookup in Excel
- 2 Comment
The Vlookup Function is a part of Excel that I personally use everyday. So many Excel users have no idea it even exists, so I thought this would be a great place to start for ExcelHints.com. Once you learn how to use vlookup, it will greatly ease many of the pains you have had in trying to group two pieces of information together.
VLookup stands for Vertical Lookup. Basically what it does it looks up a value you tell it to, against another range of cells. If it finds a match, it returns the value in the same row as the match of the column you specify. You can either have it return results for exact matches or the closest match.
Let’s start by taking a look at the formula for VLookup:
VLookup(value, table_array, index_number, not_exact_match)
value is what you are searching for in the first column of the table_array
table_array is the range you would like to look up a value for. You must include both the column you are looking for a value in and the column of the value you would like to return
index_number is the column number in table_array from which the matching value is returned if a match is found. The first column in your table_array range is 1 (Must be positive integer greater than or equal to 1).
not_exact_match tells the formula if you are looking for an exact match based on value. Enter FALSE if you want it to find an exact match. Enter True to find an approximate match, in which Vlookup will look for the next largest value that is less than value (Must be sorted in ascending order for approximate match).
Possible Errors You May Receive
#REF - If the column you are indexing is greater than the number of column in you table_array range, you will receive this Error. Also if there is a problem with you table_array reference itself, this error could result
#N/A - If you were looking for an exact match that vlookup cannot find, it will return #N/A. Also if the approximate match cannot find and approximate match it will return #N/A as well
#Value - If you type in a value for an index that is less than 1, you will get this error.
Examples for Vlookup
Example 1: =vlookup(”Shirt”,A:D,2,false) will return “111″
Another way to to do example1 is to use a cell reference for value:
=vlookup(A2,A:D,2,false) will return “111″
Example 2: =vlookup(”Jacket”,A:D,2,false) will return “#N/A”
Example 3 : =vlookup(”Shirt”,A:D,5,false) will return “#REF”
Example 4: =vlookup(”Hat”,A:D,4,false) will return “4
VLookup can also reference other sheets or even other workbooks that you need to lookup a value on. All you will need is to include a reference to that worksheet or workbook in your formula. I have found that the easiest way is to highlight the range you want looked up with your mouse while you are typing the formula and Excel will automatically fill in the reference for you.
Use IF Statements and ISNA Function along with VLookup
I will be adding sections to the website on both IF Statements and ISNA to the site, but I personally believe using VLookup and these other functions go hand in hand because I use them together so frequently.
Many times when you are using VLookups for a large range of numbers, your result will come back #N/A but you may not want #N/A in your results. For example, you may want to have the formula return a 0 if the match is not found. To do this, you need to use all these functions together. Below is an example of how this could work for you (using the table from able):
Example 1: =IF(ISNA(VLOOKUP(”Jacket”,A:D,3,false)),”Not Found”,”Found”) will return “Not Found”
Example 2: =IF(ISNA(VLOOKUP(”Shirt”,A:D,3,false)),0,VLOOKUP(”Shirt”,A:D,3,false)) will return “Blue”
Features for Future Posts
One of the features that I will explore in a future post is the ability to lookup not just the first instance that the VLookup Function finds but he 2nd or 3rd Etc. I will have to post some other Function How-To pages before I get into that though. Also using the Match() formula can be very useful to dynamically choose which row you want based on the criteria. We will explore Indexes and Array soon. Please use the comment section from my blog post to ask me any questions about this feature or give suggestions.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
Related Hints
2 Comments on this post
Trackbacks
-
Mark Oechsner said:
I have a question. I would like to look up a part number on a customer order. The left column is the customer order and in column 3 is the part number. A problem arrises when there are multiple part numbers on a Customer Order. How can I first look up the customer order, then with in that order, look up a specific part number? Thank you very much for your help.
Mark O.
February 26th, 2008 at 4:46 pm -
Lofu Martin said:
It is a fantastic thing to find a place to help a person to develop his or her in the field which is much interested for him. Actually, i would like to thank you for your support. In the last two week i visited your site and i got vital informationthat helpful to me. I just want you to sent to me more information about Excel and Access 2007. Your coopreation in doing so, will not be forgettable and thanks.
April 19th, 2008 at 10:49 am
Loading ...