By edevaney@hubspot.com (Erik Devaney)

Coordinating a massive amount of data in Microsoft Excel is a time-consuming headache. That headache can be made even worse when you need to compare data across multiple spreadsheets. The last thing you want to do is manually transfer cells using copy and paste. Thankfully, you don’t have to. The VLOOKUP function can help you automate this task and save you tons of time.

I know, “VLOOKUP function” sounds like the geekiest, most complicated thing ever. But by the time you finish reading this article, you’ll wonder how you ever survived in Excel without it.

Microsoft Excel’s VLOOKUP function is easier to use than you think. What’s more, it is incredibly powerful, and is definitely something you want to have in your arsenal of analytical weapons.What does VLOOKUP do, exactly? Here’s the simple explanation: The VLOOKUP function searches for a specific value in your data, and once it identifies that value, it can find — and display — some other piece of information that’s associated with that value.

How does VLOOKUP work?

VLOOKUP stands for “vertical lookup.” In Excel, this means the act of looking up data vertically across a spreadsheet, using the spreadsheet’s columns — and a unique identifier within those columns — as the basis of your search. When you look up your data, it must be listed vertically wherever that data is located.

The formula always searches to the right.

When conducting a VLOOKUP in Excel, you’re essentially looking for new data in a different spreadsheet that is associated with old data in your current one. When VLOOKUP runs this search, it always looks for the new data to the right of your current data.

For instance, if one spreadsheet has a vertical list of names, and another spreadsheet has an unorganized list of those names and their email addresses, you can use VLOOKUP to retrieve those email addresses in the order you have them in your first spreadsheet. Those email addresses must be listed in the column to the right of the names in the second spreadsheet, or Excel won’t be able to find them. (Go figure … )

The formula needs a unique identifier to retrieve data.

The secret to how VLOOKUP works? Unique identifiers.

A unique identifier is a piece of information that both of your data sources share, and — as its name implies — it is unique (i.e. the identifier is only associated with one record in your database). Unique identifiers include product codes, stock-keeping units (SKUs), and customer contacts.

Alright, enough explanation: let’s see another example of the VLOOKUP in action!

VLOOKUP Example

In the video below, we’ll show an example in action, using the VLOOKUP function to match email addresses (from a second data source) to their corresponding data in a separate sheet.

Author’s note: There are many different versions of Excel, …read more

Source:: HubSpot Blog

Author: Aaron