Tech
Ai
Data
News
Learning

How to do a VLOOKUP in Excel

November 23, 2021
How to do a VLOOKUP in Excel
Speak to One of our Learning Consultants Today
Talk to Sales

Using VLOOKUP successfully can feel like a major victory to many – and it should, it’s not one of Excel’s most straightforward functions to grasp; it takes practice to get it right.

VLOOKUP (Vertical Lookup) is simply a search function that identifies particular values of interest from a dataset – it’s often used when searching for information in a large spreadsheet: a name, email address, company, etc. You could use CTRL + F to search for these one by one, but it can be quite time consuming if you have a lot of data to search for!

Why would we use VLOOKUPs?

VLOOKUPs are extremely beneficial in any department of a company that works with spreadsheet data; some of the main reasons why we would use this formula could be:

  • Matching data within a spreadsheet: One dataset might contain a watch list of customers likely to end their subscription the following year – a VLOOKUP would help you to quickly identify the Salespeople assigned to these companies so they can prioritize these accounts and perhaps offer them a discount.
  • Comparing data between separate spreadsheets: One dataset might contain a master list of all the prospects in your CRM database, while the other is a list of leads from a recent Tradeshow event attended by the Sales team. You would probably want to know which of these leads on the Sales team’s list are existing leads in the CRM – a VLOOKUP would help you to identify this.

Performing the VLOOKUP function

In a nutshell, VLOOKUPs are a much more efficient way to perform searches or lookups in datasets based on specific criteria. Here’s how to do it:

  1. Firstly you must determine the information that you need, i.e. what you don’t already know. So let’s use the example above of the customers that are at risk of canceling their annual subscription. So we want to know which Salespeople are assigned to the companies on the watch list.
click to view full size

2. Next, we will type =VLOOKUP into the Salesperson column to the right of the Watch list column. The reason we type this in here is because this is the column where we want the result to appear.

After typing =VLOOKUP, we need to add the lookup value; this is the value which is common to both datasets, in this case Blammo Corp. (the company that is at risk of canceling its subscription) – we know that Blammo Corp. is on the watch list which is column J row 2, so we type (J2, – now our function looks like this: =VLOOKUP(J2,

click to view full size

3. Now we need to add the table array – this is the dataset against which you want Excel to find the information from. So we will simply select the full dataset on the left hand side because it is in this table that we will find the Salespeople who are assigned to each of these companies on the watch list. So now that we have selected the full dataset on the left hand side (type A2:H150,) the VLOOKUP function should look like this: =VLOOKUP(J2,A2:H150,

click to view full size

4. Almost there! Now we need to select the column number in which the data we’re looking for sits (in this case the Salesperson column). Excel will always assign the first column as index one, which means that the Salesperson index will be eight. Just type 8, into your function which should now look like this: =VLOOKUP(J2,A2:H150,8,. The final value we need to include is the range lookup which tells Excel that you want to find either an approximate match or an exact match. To find an exact match, which we want in this case, simply type FALSE and ). The entire function should now look like this:

=VLOOKUP(J2,A2:H150,8,FALSE)

Then you simply press enter to perform the function and see the result. If you have a number of rows to be filled in, simply anchor the array in the formula to calculate the result for the remaining cells. To do this, press F2 to go back into the VLOOKUP formula, navigate to the array (H150), press F4 and then Enter. The array should now look like this: $H$150.

click to view full size

Now simply copy and paste this function into the remaining empty cells below to see the results. The watch list will then be populated with the correct Salesperson data and the assigned company accounts.

click to view full size

VLOOKUP Limitations

It’s important to note that despite being an incredibly beneficial and efficient way to analyze datasets quickly, there are some limitations with VLOOKUPS:

  • The VLOOKUP formula must be formatted in a certain way for it to work correctly; the lookup array must be on the left-hand side of the output. In other words, our formula would not have worked had the Salesperson column we were populating not been to the right of the lookup array, i.e. the dataset table we were drawing information from.
  • Secondly, performing a VLOOKUP is not advised when working with very large datasets as it takes much longer to perform and in some cases even causes Excel to crash. If you are working with a large dataset of 20K+ rows, try using INDEX and MATCH lookups instead; while more complex to write, they offer much faster calculations.

Nonetheless, being able to perform the VLOOKUP formula can save Execs a huge amount of time and make their work far more efficient when using spreadsheets.

The information in this post was taken from the Lookups and Database Functions Excel course on Kubicle. You can get access to this course by signing up for our free trial.

Kubicle offers a range of courses in Excel: beginner to advanced

350+ lessons and 80+ exercises available

Topics Include: Excel Essentials | Data Analysis | Charts and Dashboards | Modeling for Business Investment Valuation | Macros and VBA | Power Pivot and Power Query Essentials | DAX in Power Pivot | Simulation in Excel | Financial Modeling Essentials | Advanced Financial Modeling

We offer a 7 day free trial to all learners. Sign up now!

Most Recent

How to Create a Sources and Uses Table for a Leveraged Buyout Transaction
Tech
Ai
Data
News
Learning
The Good, the Bad and the Ugly of HR Analytics
Tech
Ai
Data
News
Learning
5 Ways You Can Maximize the Impact of Your Charts
Tech
Ai
Data
News
Learning

For nearly a decade, Kubicle has set new benchmarks in corporate training, empowering over one million learners across more than 1,000 enterprises worldwide. The mission has always been to foster valuable, measurable skills that drive organizational success.

However, teaching new skills alone isn’t enough to create lasting change. To bridge the gap between learning and real-world impact, businesses require more robust, integrated approaches. That’s why Academies+ has been developed—an innovative blended learning program designed to transform skill acquisition into tangible outcomes.

Why Blended Digital Learning?

eLearning alone is a proven tool for effectively upskilling large groups. Organizations leveraging Kubicle’s solutions often see significant performance improvements after just a few hours of tailored training. However, learning a new skill is only half the battle. The real challenge is ensuring those skills are applied effectively within an organization’s unique context to maximize its impact.

In recent times, an additional challenge has emerged: the rapid acceleration of change, especially in technology. The production timelines for eLearning courses often struggle to keep pace with product development, nowhere more obviously than in the domain of Generative AI. As a result, a course being developed today may already be outdated by its release—regardless of how short the production cycle is.


Blended learning tackles these challenges by merging the advantages of eLearning with hands-on, real-world applications. Likewise, virtual classrooms led by industry-leading SMEs offer an effective platform for delivering consistently up-to-date content. This approach enhances knowledge retention while ensuring accuracy and smooth integration into daily workflows.

How It Works: The Academies+ Formula:
Self-paced eLearning + Virtual Classrooms & Workshops + Impact Coaching + Impact Project = Maximized Organizational Impact

Our unique design of blended learning interventions are aligned to one thing, Impact.

Learners start by exploring foundational concepts at their own pace with self-guided eLearning. This flexible format allows them to absorb and reflect on the material before moving on to interactive virtual classrooms. Here, subject matter experts and peers provide fresh insights, helping to deepen understanding through discussion and collaboration.

The experience continues with hands-on workshops, where learners put their new skills to the test in a practical setting, guided by experts. But the real differentiator of Academies+ lies in its final components: the Impact Project and the Impact Coach.

The Impact Project is where learning meets real-world impact. Through the Impact Project, learners will address genuine business challenges and make a measurable difference. With the support of an Impact Coach, participants develop a project tailored to their organization’s needs, applying their newly acquired skills to solve genuine problems and deliver tangible results.

With Kubicle’s Academies+, we don’t just train—we transform, turning learning into a strategic asset for your organization.

Launching Now: AI for Business Professionals Academy+

In 2025, Kubicle will roll out a series of Academies+ programs addressing critical skill gaps in organizations. The first program, AI for Business Professionals Academy+, is now open for enrollment.

This program is designed to equip leaders and professionals with the skills to harness AI effectively in their roles. As regulations like the EU AI Act approach, organizations must meet AI literacy standards. This program prepares participants to not only understand AI principles but also apply them to improve productivity and efficiency.

By the end of the course, participants will view AI not as a buzzword but as a practical tool for solving business challenges and driving innovation.