VLOOKUP is a powerful and much-used Excel function. If you’re not familiar, it finds a lookup value in a table and returns the corresponding value of another column. This is great for looking up information by reference. In the example image above, Name is the lookup value, and it returns the matching Salary. If you are a power Excel user, you are likely very familiar with this function. If not, here’s how it works:

  • =VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup))
    • Lookup_value – The value you want to find in the lookup value column
    • Lookup_value – The table range containing columns for both the lookup and return values
    • Col_index_num – The index number for the column containing the return values
    • Range_lookup – The type of match: Nearest Less Than (TRUE), or Exact (FALSE) [optional]

VLOOKUP, though powerful and useful, is not without its limitations. In fact, it can be dangerous because of some unexpected effects and behaviors. If you use VLOOKUP, please read on to learn about these issues. This equally applies to VLOOKUP’s sister function HLOOKUP (which does horizontal lookups instead of vertical lookups) and another similar function, LOOKUP. I have provided a safer and more flexible alternative lookup method, which I use virtually everywhere in place of VLOOKUP.

First, I’ll explain some of VLOOKUP’s limitations:

  1. Your data range is limited to a table. That means the data you are looking up has to be in a standard tabular form. You cannot use VLOOKUP to find a lookup value in a different table, sheet, or offset row. This limits the ways you can display your data, as anything you want to lookup must be available in a standard table format in your spreadsheet.
  2. VLOOKUP always searches the leftmost column of the specified table to find the lookup value. Again, this limits your choices in presenting data as lookup values always have to be to the left of the return values. This sometimes means you must have multiple copies of tables in order, think far ahead when creating tables that might be used in lookup, or reorder columns after the fact simply to use VLOOKUP.
  3. You can only specify the return value column by index number. This means there is no way to include a static reference to the return value column. If someone adds a column between the lookup value column and return value column, it will break your VLOOKUP and you have to manually increase the column index number in the formulas. This is a maintenance nightmare.
  4. VLOOKUP provides a very limited approximate match feature. The only aproximate match option finds the nearest “less than” value. Unless you want that type of behavior, you’re out of luck and can’t use it.

Now, here are some dangers of VLOOKUP:

  1. By default, VLOOKUP uses approximate match. If this is how you want it to function, then great… However, in many cases you want an exact value returned. It gives no indication it is picking a closest match result. If you do not want this behavior (which is most of the time, I have found…), you remember to explicitly set the Range_lookup argument in the formula to FALSE. Range_lookup is optional, and not a very descrive name of this feature, so it is often overlooked. This quirk is exasperated by the second danger…
  2. VLOOKUP can provide false results if the table is not sorted in ascending order! This is an issue when you use the approximate match feature, which is TRUE by default. Basically, VLOOKUP starts at the top of the table and goes down row by row until to gets to a valie less than or equal to the lookup value. If your table is not sorted in ascending order, this can give false results, as the formula stops processing rows immediately after finding a “match.”

Sufficiently scared? Checking your spreadsheets for issues now?? The answer to these problems and limitations is the INDEX-MATCH lookup method. This methods uses two functions together to provide a more safe and flexible lookup feature. Here’s how each function works, independently:

  • INDEX returns the value at the intersection of a row and column in a given range.
    • Formula: =INDEX(Array, Row_num, Column_num)
      • Array – The range of cells
      • Row_num – The row to return data from
      • Column_num – The column to return the data from [optional]
  • MATCH returns a position of an item in an array that matches a value.
    • Formula: =MATCH(Lookup_value, Lookup_array, Match_type)
      • Lookup_value – The value you want to find in the lookup value array
      • Lookup_array – The range containing lookup values
      • Match_type – Exact (0), Nearest Less Than (-1), or Nearest Greater Than (1) [optional]

Combining the two functions, you are able to effectively beat the limitations and dangers of VLOOKUP. You can build a lookup that allows you to specify the lookup column and return value column completely independently, and also control of the nature of the approximate match, if not exact. The arrays are ranges and you can specific Column_num in place of Row_num, which mean you are not limited to using columns only; this can be used in place of HLOOKUP. It still has the approximate match feature as nearest “less than” by default, and carries the same sort warnings as VLOOKUP. Here’s how the combined function works:

  • =INDEX(Return_value_range, MATCH(Lookup_value, Lookup_value_range, Match_type))
    • Return_value_range – The range that holds the return values
    • Lookup_value – The value you want to find in the lookup value array
    • Lookup_value_range – The range containing lookup values
    • Match_type – Exact (0), Nearest Greater Than (-1), or Nearest Less Than (1)

Enjoy, and use in good health!

Example of a INDEX-MATCH formula exposed in an Excel spreadsheet

Like this post? Share it!