If you work with spreadsheets, you've probably used the VLOOKUP function at some point. Since its creation in 1985, it has been one of the most widely used formulas in Excel for its ability to look up data vertically to the right. This function allows you to look up a value in a table based on specific criteria. For example, you could use VLOOKUP to find the name of a customer based on their customer ID. Let’s take a look at VLOOKUP.

VLOOKUP

This formula allows you to find a value from the left column and gives you a value in the same row from the column number that you choose in the formula to the right. It is widely used by finance and other business professionals for its ability to match data in a large data set. VLOOKUP can search for numeric or string data and returns either numeric or string data.

Syntax

The structure of the formula is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, approx)

  • lookup_value - the leftmost value to use for searching the array
  • table_array - the cell range that has the data that you want to search
  • col_index_num - the column for the value that you want
  • approx - Boolean (True or False) value for getting an approximate match or an exact match; if no value is provided that default will be TRUE for an approximate match
  • If approx is FALSE, it finds an exact match, not an approximate match.
  • If it cannot find one, it returns an #N/A error value.
  • If approx is TRUE or omitted, and the value cannot be found, then the largest value that is less than the value is used.

Examples

One great way to use VLOOKUP is to use the customer's last name to find the total spent. Below you can see that we use: =VLOOKUP(F3,A2:C98,3,FALSE) to get the total spent from the last name you need to lookup.

Basic VLOOKUP formula use case

Other uses for VLOOKUP include:

  • Classifying and Categorizing Data
    • Use a key to assign the category
  • Partially Match using wildcards
    • Wildcards: (*) and (?)
  • Replace Nested IF statements

Limitations

VLOOKUP can do many things but there are some notable limitations as well. VLOOKUP only looks right, so the data that you are working with must be formatted with the unique value to search for on the leftmost column. VLOOKUP searches for the first match and defaults to an approximate match rather than an exact match. VLOOKUP is also not case-sensitive and will treat ROSA and rosa the same. Inserting a column in your spreadsheet can be dangerous and cause the formula to break. Finally, VLOOKUP is limited to one search condition.

Conclusion

VLOOKUP is an entry-level Excel formula and regularly saves professionals time and effort. These practical tips shared in this post will give you an edge when implementing VLOOKUP, as well as make you more efficient with spreadsheets. Never underestimate the value of an effective VLOOKUP strategy, whether you need to find one piece of data or many VLOOKUP is there to help!

Now that you know some of the pitfalls of using VLOOKUP, go ahead and use it with confidence. Just be aware that there are a few limitations so you won't be surprised when you run into one!