Sign in Subscribe
Data Analitics Study Notes

VLOOKUP {data aggregation}

Aleksandr Kosenko

Aleksandr Kosenko

— 4 min read
VLOOKUP {data aggregation}

VLOOKUP core concepts

Spreadsheet functions can be used to quickly find information and perform calculations using specific values. VLOOKUP, or Vertical Lookup, is one such function that vertically searches for a certain value in a column to return a corresponding piece of information. In this reading, you’ll examine the intricacies of this extremely useful function so you understand how it works when you use it to analyze data.

VLOOKUP functionality

VLOOKUP searches for a search term, called a search_key, in one column of a spreadsheet. When the search_key is found, the function returns the data from another column of the row from which it was located. VLOOKUP returns only the value that corresponds to the first item it matches. So, if there are multiple matching values, the spreadsheet will return only data about the first one.

VLOOKUP use cases

Here are two common reasons why you might use VLOOKUP:

  • Populating data in a spreadsheet. Perhaps a store manager is tracking incoming shipments before a busy holiday. They could use VLOOKUP to look up product ID codes in a product spreadsheet and retrieve the corresponding product information from another spreadsheet. This would help the manager know how many stock clerks they need to schedule to work when the shipments arrive.
  • Merging data from one spreadsheet with data in another. If a teacher keeps one spreadsheet for student grades and another for attendance, they could use VLOOKUP to combine the spreadsheets. That way, they could search for a particular student in the attendance sheet, and VLOOKUP would pull the corresponding attendance record into the grades spreadsheet.

VLOOKUP syntax

VLOOKUP is available in both Microsoft Excel and Google Sheets. Here, you’ll explore its syntax in Google Sheets. Refer to the resources at the end of this reading for more information about VLOOKUP in Microsoft Excel.

VLOOKUP’s syntax is:

VLOOKUP(search_key, range, index, is_sorted)

The following sections explain each of the four parts of the syntax.

search_key

This is the value the function will search for. It can be a number, text string, or cell reference.

range

This is the range of cells over which the function will search and return information. The first column in the range is searched. When the search key is found, the index from that row is returned.

For example, if you search for the search_key in column B and return the data from column D, the range would need to include columns B through D, such as the range B2:D10. If you specified a range of A2:D10, the function would search for the search term in column A.

The search_key must be to the left of the information you want the function to return. This may require you to move columns around before you use VLOOKUP. For example, if you plan to search for the search_key column D, but the information you want the function to return is in column A, you must rearrange your columns before using VLOOKUP.

index

This is the position of the column that contains the data to be returned. The first column in the range is column number 1, and each column is numbered sequentially to the right.

For example, if the range is B2:D10 and you want to return a value from column D, the index number would be 3. If the index is not between 1 and the number of columns in range, the error message #VALUE! will be returned.

is_sorted

This indicates whether to return an approximate or exact match. For example, if you’re searching for Google, then google would not count as a match.

  • To return an exact match, set is_sorted to FALSE. This is recommended.
  • To return an approximate match, set is_sorted to TRUE. The nearest match (less than or equal to the search_key) is returned. To use this option to obtain accurate results, you must sort your data in ascending order. But, you could still find a value.
  • If neither TRUE nor FALSE are selected, the function will default to TRUE.

The #N/A error

#N/A indicates that a matching value can't be returned because no matches were found.

Identify and fix common VLOOKUP errors

  • VLOOKUP function only returns the first match it finds and can only return a value from the data to the right.
  • Copying and pasting a column to the left of the data can solve the issue of not being able to look left.
  • Absolute reference: A reference that is locked so that rows and columns won't change when copied
  • Locking or making absolute the table array part of the function can solve issues with the function not working when dragged down the column.
  • Version control issues can be mitigated by locking the spreadsheet or using MATCH function. MATCH: A function used to locate the position of a specific lookup value
  • Using TRUE or FALSE within the VLOOKUP function controls whether it looks for approximate or exact matches.
  • Troubleshooting involves asking key questions such as how to prioritize issues, clearly stating the issue faced, identifying resources that can help solve the problem, and finding ways to prevent the problem from recurring in the future.

Troubleshooting questions

  • How should I prioritize these issues?
  • In a single sentence, what's the issue I'm facing?
  • What resources can help me solve the problem?
  • How can I stop this problem from happening in the future?

Key takeaways

Use VLOOKUP to search for a value in a column and return a corresponding piece of information. It’s a very useful tool for data professionals, as it enables them to combine data from multiple sources and find information quickly. Keep in mind that the column that matches the search_key in a VLOOKUP formula should be on the left side of the data. The range must include both the column being searched and the column that contains the information being returned. TRUE means an approximate match, and FALSE means an exact match on the search_key.

VLOOKUP resources for Microsoft Excel

VLOOKUP may slightly differ in Microsoft Excel, but the overall concepts can still be generally applied. Refer to the following resources if you’re working with Excel:

Read more