Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada. As mentioned on the show, you can get a free chapter a week from the new book by signing up here.
A lot of people try to use Excel as a database. While it can work as a database, some of the tasks that would be very easy in a database program are fairly complex in Excel. One of these tasks is matching two lists based on a common field; this can be easily accomplished using Excel VLOOKUP. You will find the function VLOOKUP to be extremely useful so check out an example of when and how to use this function below.
Say that your travel agency sends you a month end report of all the places that your employees have traveled. The report uses airport codes instead of city names. It would be helpful if you could easily put in the real city name instead of just the code.
On the Internet, you find and import a list showing the city name for each airport code.
While this example worked out perfectly, when viewers use VLOOKUP, it usually means that they are matching up lists that came from different sources. When lists come from different sources, there can always be subtle differences that make the lists hard to match. Here are three examples of what can go wrong and how to correct them. A) One list has dashes and the other list does not. Use the =SUBSTITUTE() function to remove the dashes. The first time that you try the VLOOKUP, you will get N/A errors. To remove the dashes with a formula, use the SUBSTITUTE formula. Use 3 arguments. The first argument is the cell containing the value. The next argument is the text that you would like to change. The final argument is the replacement text. In this case, you want to change dashes to nothing, so the formula is =SUBSTITUTE(A4,"-","").
You can wrap that function in the VLOOKUP to get the description. B) This one is subtle, but very common. One list has a trailing blank space after the entry. Use =TRIM() to remove excess spaces. When you initially enter the formula, you find all of the answers are N/A errors. You know for sure that the values are in the list and everything looks OK with the formula. One standard thing to check is to move to the cell with the lookup value. Press F2 to put the cell in Edit mode. Once in edit mode, you can see that the cursor is located one space away from the final letter. This indicates that there is a trailing space in the entry.
To solve the problem, use the TRIM function. =TRIM(D4) will remove leading spaces, trailing spaces, and will replace any internal double spaces with a single space. In this case, TRIM works perfectly to remove the trailing space. =VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE) is the formula. C) I mentioned a bonus tip in the show notes: how to replace the #N/A result for missing values with a blank. If your lookup value is not in the lookup table, VLOOKUP will return an N/A error.
This formula uses the =ISNA() function to detect if the result of the formula is an N/A error. If you get the error, the 2nd argument in the IF function will tell Excel to put in any text you wish.
VLOOKUP allows you to save time when matching lists of data. Take the time to learn the basic use and you will be able to do far more powerful tasks in Excel. For the BEST TV show on technology, check out Call for Help. This tip was originally published on June 3, 2005 and aired on TechTV on July xx, 2005. The permanent URL for this page is http://www.mrexcel.com/tip096.shtml. If you are looking for show notes from another episode, visit my complete list of TechTV appearances.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
Excel is a registered trademark
of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.
All contents Copyright 1998-2008 by MrExcel Consulting.