There are a lot of easy ways to make vlookup functions in Excel more functional.  I find that a fair amount of people have difficulty getting a vlookup function to work in the manner they wanted it to, so I was thinking that first it might help to really understand the purpose and the four basic parts to the function.  The purpose of a vlookup function (vlookup means “vertical lookup”) is to retrieve a piece of data in a table by using a unique identifier as a reference.  The fact that it is vertical means that the unique identifier must be arranged in a column of the data table you are referencing.

The Four Parts of a vlookup function

  1. lookup_value: this is the unique identifier.  It must be a piece of data in the reference data table.
  2. table_array: this is the reference data table.  Do not include row or column headers, only include the data itself.  The first column of the table_array must always contain the lookup_value and be sorted in ascending order based on the first column.  This will prevent a large number of lookup failures.
  3. col_index_num: assign a number (1,2,3,…) to the columns in the table_ array from left to right, and indicate the number of the column that contains the piece of data you are trying to retrieve.
  4. [Range_lookup]:  you must indicate either “true” or “false”.  “True” means that the lookup_value does not have to exactly match the data in the first column of the table_array in order to produce a result.  “False” means that the lookup_value must exactly match the data in the first column in the table_array in order to produce a result.

Tip:  Many times failures in the lookup occur because the formatting of the lookup_value is not the same as the formatting in the first column of the table_array.  Ensure that the formatting of these two columns are identical.

How do I get rid of the annoying #N/A results when the lookup doesn’t find the lookup_value in the table_array?

Good question.  I find that there are usually many different ways of achieving the same goal when using Excel, but here is my solution.  Embed the vlookup function in both an IF and an ISERROR function and you have the ability to determine what results display when both the vlookup function does and does not have a result.  In this way, if you are trying to lookup numerical data such as dollars, you have the ability to make the formula result in a 0 instead of the dreaded #N/A (that which always ruins your sum formulas!!!)

Example: This is the formula result when using the parameters of a standard vlookup as shown above.

The #N/A indicates that the invoice number being looked up was not found in the reference data table, or table_array.  These error results will prevent you from being able to perform numerical calculations.  Instead, embed the vlookup function inside an ISERROR function, and use that as the first argument in an IF function.  Sounds confusing?  Let’s break it down.  (The syntax for this compound function is at the top of the image below)

Basically, what you are saying with this formula is, “If this vlookup function doesn’t work, return zero as the value; if the vlookup function does work, then return the vlookup function’s result.”  Below is an image of what this formula’s result is using the same information as the image above.  Notice the #N/A results are now zeroes.  This will enable the user to add numerical calculations without getting errors.

Get creative with this one particular compound function.  You have the ability to tell the spreadsheet what to do if the vlookup works and if it doesn’t work, so imagine the possibilities.  If you were looking up text strings, for example, you can have this function return “Record Not Found” instead of zero as in the example.  This type of function returns better visual quality results and allows for minimization of extraneous data manipulation such as finding and replacing, additional sorting, etc. required to produce numerically calculable results.

Interested in learning more? …check out the tutorial page for some more Microsoft Excel techniques.

Popularity: 40% [?]