MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel VLOOKUP

June 03, 2005

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.

But, how do you get this information on each record in the report?

  1. Use the VLOOKUP function. VLOOKUP stands for “Vertical Lookup”. It can be used anytime that you have a list of data with the key field in the left-most column.
  2. Start to type the function, =VLOOKUP(. Type Ctrl + A to get help with the function.
  3. VLOOKUP needs four parameters. First is the city code in the original report. In this example, that would be cell D4
  4. The next parameter is the range with your lookup table. Highlight the range. Be sure to use F4 to make the range be absolute. (An absolute reference has a dollar sign before both the column number and row number. When the formula is copied, the reference will continue to point towards I3:J351.
  5. The 3rd parameter tells Excel in which column the city name is found. In the range of I3:J351, the city name is in column 2. Enter a 2 for this parameter.
  6. The 4th parameter tells Excel if a “close” match is OK. In this case, it is not, so enter False.
  7. Click OK to complete the formula. Drag the fill handle to copy the formula down.
  8. Because you carefully entered absolute formulas, you can copy column E to column D to get the destination city. In this case, all of the departures are from Pearson International Airport in Toronto.

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.

  1. 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.

  2. 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.

  3. 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.

    =IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))

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.