Excel Vlookup Function Gives You X-ray Vision
Free Microsoft Office Tutorials
Have some fun with the Excel Vlookup function
One of the most popular functions is the Excel Vlookup function. I am going to discuss the use of the Excel Vlookup function in Microsoft Excel 2003. It should be noted however that this information will apply equally to Microsoft Excel 2007 and 2010.
Watch this short tutorial
What is the Excel Vlookup function? The Vlookup function looks up data in a list based on a criteria or value that you specify. And then give you an adjacent value to the lookup value. The Vlookup function always references the column on the left. The lookup value that you use must always be located in this column. Look carefully at this illustration and you will notice that allows for four arguments.
- The criteria or “ look up value” in this instance it is “C” in cell G5.
- The second argument is the data array. This would not include the headers.
- The third is the column that you want the information to come from in this instance it is column 4.
- The fourth argument is whether you want an exact match or an approximate match. This argument can be omitted or you can use False or 0 the exact match or True or 1 for an approximate match.
A Top Tip here is to put your first column range the criteria or “look up value”into a drop-down list to make Vlookup dynamic.
You can use named ranges with the look up. In fact it is highly recommended. The named range takes the place of your second argument the data array.
It is possible to have your list on one sheet and the multiple criteria to be looked up on another sheet. As an example you might on one sheet have a complete list of work codes in a column and the column to the right of that column you may have listed all of the times that those codes represent. This list of codes and times could be quite long. In this case we would dedicate a sheet to that list and look up the data on another sheet. It is not necessary for anyone to even know that that list exists. Look at this illustration. You can see that it is looking up multiple criteria from a sheet called List.
Watch this short tutorial on the Excel Vlookup function
Here you will also see that a named range "code_range" is used for the data array.
Vlookup #N/A errors
A common problem with the Vlookup function is missing data. When this occurs you will get this error #N/A. There are multiple ways to deal with hiding this error. I have shown you below how to do this in a formula. We would use the IF function and the ISNA function. Basically the formula says that IF the data is an ISNA then leave the cell blank.
You'll find you can not live with out the Vlookup function. The Index and Match functions when used together are a bit more versatile. Have a look at how they work. Index and Match Functions