Get to the heart of your data faster with VLOOKUP in Excel

Sprint to the finish line with this essential data analysis function.

When it comes to analysing data, I love a trick that will speed things up and get me to the heart of my data faster. As much as I love Excel, sitting at the computer for hours on end manually sifting through data is not my idea of fun. 

Now you might be thinking, “Well Traci, if you don’t enjoy manually trawling through data, why are you so obsessed with Excel?”.

Here’s why – formulas and functions are the real magic of Microsoft Excel – they’re the juicy bits that get me so excited about spreadsheets. These adaptable ‘questions’ perform calculations, return information, manipulate the contents of other cells, test conditions (the list goes on!) and make it quick and easy to get results – which is right up my street.

There are so many incredible ways you can create and combine formulas and functions to help make all aspects of your life, both business and personal, easier. 

VLOOKUP (short for vertical lookup) is a powerful function in Excel that allows you to search for specific data points in a table and retrieve related information from another column.

It’s super helpful when you’re dealing with large datasets and need to find specific values quickly (yes please).

So, let’s take a closer look at VLOOKUPs, what they involve and how you can use them to get to the heart of your data faster!

Understanding the VLOOKUP function

To put it simply, the VLOOKUP function helps you to find things in a table or a range by row. 

For example, if you need to find the phone number of a customer and you know their name, you can perform a VLOOKUP to find the phone number by searching their name.

In its simplest form, the VLOOKUP function looks like this:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

So what do these elements mean? I’ll break it down for you: 

• lookup_value: This is the value you want to look up. This can be a value or a reference to a cell, but it must be located in the first column of the range of cells you specify in the table_array argument.

• table_array: This is the range of cells that contain the data you want to look up. The table_array will search for the lookup_value and return value. The first column of the cell range must match that of the lookup_value (e.g. if your range is B2:E9, your lookup_value must be in column B). 

• col_index_num: This indicates the column number you want to retrieve your data from. 

• [range_lookup]: This is an optional argument that specifies whether you want an exact match or an approximate match. Enter TRUE for an approximate match or FALSE for an exact match.

When you combine all of these elements together you can instruct Excel to pinpoint a specific piece of information for you from a vast pool of data.

Navigating your way through a VLOOKUP

Let’s stick with our customer example above. You have a large dataset with customer information, including names, addresses and contact details, and you need to find the phone number of a particular customer whose name you know. 

Without VLOOKUP you might have to trawl through hundreds or thousands of rows of data to find this information. This can be time-consuming and like finding a needle in a haystack – plus it’s much more prone to error. 

With VLOOKUP you can locate that phone number in seconds, saving you time and minimising the risk of error. 

By using the customer name column as the lookup_value, you can instruct Excel to search for the name in the specified table_array. Once a match is found, VLOOKUP will then retrieve the phone number from the specified column (col_index_num) and display it in the cell where you entered the formula. 

This ability to pinpoint specific information within a vast dataset makes VLOOKUP an invaluable tool for data analysis. 

For more advanced Excel users, you can also use VLOOKUP to combine data from several tables into one worksheet. You just need to ensure that one of the tables has fields in common with all the others.

This is incredibly useful if you need to share a workbook with people who have older versions of Excel that don’t support features with multiple tables as data sources. What it means is that you can combine the sources into one table and change the data feature’s data source to match the older version so that your worksheet can be accessed across your team.

Tips for optimising your VLOOKUP

While VLOOKUP is an incredibly useful function, like all formulas and functions, if you aren’t providing it with the correct information, then it won’t work effectively. 

To make sure you’re getting the most out of your VLOOKUP, here are a couple of ‘best practices’ that you should keep in mind to help enhance its efficiency: 

Sort your data

For VLOOKUP to work accurately, you should make sure that your data is sorted in ascending order based on the column you’re using as the lookup value. Sorting your data can significantly improve the speed and accuracy of the lookup process.

Make sure your data doesn’t contain errors

Certain characters will create errors when trying to use a VLOOKUP. Make sure the data in the first column doesn’t have: 

• Leading spaces
• Trailing spaces
• Inconsistent use of straight and curly quotation marks
• Nonprinting characters
• Formatting (numbers vs text)

If these elements are found within your data it could return an unexpected value from your VLOOKUP.

Use absolute references

When using VLOOKUP it’s crucial to lock the references to the table array by using absolute cell references. This ensures that the range remains constant when you copy the formula to other cells, preventing any discrepancies in the lookup process. 

TIP: Avoid the need for this entirely by using Named Ranges instead.

Don’t store number or date values as text

Make sure the data in the first column of your table_array isn’t stored as text values. If it is, your VLOOKUP might return an incorrect or unexpected value.

Utilise wildcard characters

By incorporating wildcard characters like asterisks (*) and question marks (?) into your lookup value, you can perform approximate matches or search for patterns within the data. This enables you to expand the functionality of VLOOKUP, allowing for more flexible searches. 

These wildcard characters only work if the range_lookup is FALSE and the lookup_value is text. 

A question mark matches any single character and an asterisk matches any sequence of characters. (But if you want to find an ACTUAL question mark or asterisk, you’ll need to input a tilde (~) in front of the character, i.e. ~? or ~*). 

These wildcard characters mean you can look up degrees of variable data. For example, if your lookup_value is ACE, =VLOOKUP(“AC?”,B2:E7,2,FALSE), will look at all instances of ACE with a last letter that could vary.

Summary

Excel’s VLOOKUP function is a truly wonderful tool. Over time it’s going to save you HOURS of searching through spreadsheets and will prevent you from going crossed-eyed while analysing your data.

The benefits of this function mean that you can navigate through large datasets, retrieve specific information, and gain valuable insights quickly.

By mastering VLOOKUP, you can save time, reduce errors, and enhance your productivity when working with data. 

Now that you’re armed with VLOOKUP you can dive into your data with confidence and let this ACE function guide you to the heart of your data faster, and more reliably than ever before.

Watch out for future articles, where we will explore the INDEX/MATCH formula and also the newer XLOOKUP formula, which can both be used as alternatives to VLOOKUP.

Find out how to utilise Excel to transform your business. Book a FREE consultation today to get started.