How To Use Lookup Functions in Microsoft Excel?

Lookup Functions in Microsoft Excel: A Must Know For Every Analyst & Manager

Yesterday I wrote about the Text Functions in Microsoft Excel, and many of you contacted me for training on the Text and other useful Functions in Excel. Most of the people want to learn the Lookup functions in Excel – namely the Vlookup, Hlookup, and the other Lookup functions that are available in Excel 2016, Excel 2019 and Office 365 edition of Excel.

The most popular lookup functions in Excel (VLOOKUP & HLOOKUP) have been there since the first version of Microsoft Excel, and have stayed the same throughout. They have been the reason for people to learn and train on how to use Excel effectively.

I have seen that there are usually one or two people in the company who know how to use the Vlookup function. And they are revered for it. Others go to them for Excel sage advice.

And if you ask them to teach you about the Vlookup function, they claim that it is too difficult for you to learn, and that you’d be better off if you just let them code it out for you. This way they retain their sole control over the Excel users as the Guru, and they try to be indispensable by this virtue.

Well, today, you can learn about the Vlookup function in no time.

In our 2 or 3 day Advanced Excel classes in Singapore, we teach how to use the Lookup functions in Excel, what are the variations, when to use which type of function, and even the limitations of the Lookup functions  in Excel… Yes, the Lookup functions that we all love to use also has its limitation. It can’t be used all the time.

What is VLOOKUP?

VLOOKUP, short for “Vertical Lookup,” is one of the most widely used functions in Microsoft Excel. This powerful tool allows users to search for a specific value in the first column of a range or table and return a value in the same row from a specified column.

The VLOOKUP function simplifies the process of finding data within large spreadsheets, making it a critical component for anyone working with extensive datasets.

Why is VLOOKUP So Important?

The importance of VLOOKUP lies in its ability to streamline data management and enhance productivity. In many professional settings, users deal with large amounts of data spread across multiple columns and rows.

Manually searching for specific information can be time-consuming and prone to errors. VLOOKUP automates this process, allowing users to quickly locate and retrieve data, thereby improving accuracy and saving valuable time.

VLOOKUP is essential for tasks such as:

1. Data Analysis: By efficiently retrieving specific data points, VLOOKUP aids in analyzing large datasets, enabling users to draw meaningful insights and make informed decisions.
2. Data Consolidation: When combining data from multiple sources, VLOOKUP can help match and merge information based on common identifiers, such as product IDs or customer numbers.
3. Error Reduction: Automated lookups reduce the risk of human error, ensuring that data retrieval is consistent and reliable.

When to Use the Vlookup Function in Excel:

This function can be used to pick the price, description, etc. for any part or the employee’s name, address, basic pay etc.

If you key in the product code, or the employee code, Vlookup can then lookup the correct price or description from the same row. The function basically will match the value in the first column, and lookup the relevant column from the same row of the lookup table, and present it to you.

This is extremely useful, as we can write a dynamic formula, which picks up the relevant value based on any key.

Variations of Vlookup Function in Excel

The Vlookup function can be used to match up an exact value, or an approximate value.

Both of these variations can save you immense time and are extremely quick to use.

The syntax of the Vlookup formula is VLOOKUP(what to lookup, Where to lookup,  Which column to pick, approximate match or exact match flag)

With these 4 options specified, the Vlookup formula can be your secret weapon, your swiss army knife that gives you the edge in the workforce and makes you invincible!

Let’s say we have a table of employee numbers, and their names. Upon matching the employee number, I want to pick up the employee name. How can Vlookup do this

Employee Id  Employee Name

123                    John Smith

456                   Rebecca Tan

789                   Robert Lim

To pick up the name of the employee upon submitting the name, we can write

=Vlookup(456, A1:B3, 2, 0)

This means that we want to lookup the id of 456 in the lookup table beginning from row A1 to Column B3. Once matched, we want to pick up the second column, denoted by the 2, which is the name. And yes, we only want the name if the ID matches exactly. This is indicated by the last 0 in the formula. The ZERO (0) in the formula stands for an Exact Match indicator.

Once you write this VLOOKUP formula, the name of Rebecca Tan will popup up immediately. And if you substitute 456 with 789, the name will change to Robert Lim.

It is that simple… This Vlookup function works in all the versions of Excel – on the PC and on the Mac.

So if you have Microsoft Excel 2013 or Microsoft Excel 2016, or Microsoft Excel 2019, or maybe you have purchased the online subscription to the Microsoft Office 365 version, the VLOOKUP function works the same on all these versions.

How To Use VLOOKUP for an Approximate Match

If you don’t have an exact match in your lookup table, we can still use VLOOKUP. This is useful when we have a range of values that match a particular value.

For example, let’s take the case of a class that takes an exam, and we have to prepare the result. The result grade will be based on the marks. Most likely, the different people who took the test will get different marks, and their grades will be different.

So the grades table can be setup as this:

0 Fail
50 D
60 C
70 B
80 A
90 Distinction

This means that if a student score any marks below 50, they will be deemed Failed. And anything from a score of 50 to 59.999 will give you a D grade. Similarly, at 90 or above, one will be receiving a Distinction. So with the Lookup table set, we can now write our VLOOKUP function.

=VLOOKUP(Students_Marks, Lookup_table, Column_Index_to_pick, TRUE)

In this pseudocode,

1. the Students_Marks are the marks received by a student.
2. Lookup_table referes to the grades table we setup earlier.
3. The column_index is for the column we want to pick… which is 2, for we want the grade (from Fail, to D, C, B, A or Distinction).
4. The last attribute in the formula – TRUE, denotes that this is going to be an approximate match.

So if a student secures 63 marks in the test, the formula will look like this:

=VLOOKUP(63, A1:B6, 2, TRUE)

This formula, when executed will generate the grade of C, which is correct. One gets a C if the marks are greater than or equal to 60, but less than 70.

Now this VLOOKUP formula will generate the correct grade, irrespective of the participant who may secure a 63 or a 63.5 or a 63.78. The grade will always turn out to be correct.

This is the use of the VLOOKUP function in Excel, with an Approximate match. 

You must learn both variations of VLOOKUP for any version of Excel to be more effective and efficient with using Lookup functions in office work.

Similarly, you can use the HLOOKUP Function for Exact match or approximate match. The only thing different is that the lookup table is a horizontal table.

Don’t worry, we have examples, exercises and multiple scenarios to go through both VLOOKUP and HLOOKUP in our training courses.

And we cover them in great detail, so that you can master these Lookup functions of Excel that are in high demand in the job market for Excel jobs.

Use Cases for VLOOKUP

1. Sales Reporting: Imagine a sales manager who needs to compile a report that includes sales figures, product descriptions, and customer names from different tables.

   With VLOOKUP, the manager can easily pull this information together by referencing the product ID or customer number, creating a comprehensive and accurate report in minutes.

2. Inventory Management: A retail store manager might use VLOOKUP to keep track of inventory levels.

   By using product codes as the lookup value, the manager can quickly retrieve current stock levels, reorder points, and supplier details from a larger inventory database.

   This ensures that the store remains well-stocked and can respond promptly to customer demands.

3. Human Resources: In an HR department, VLOOKUP can be used to match employee IDs with their corresponding personal information, job titles, and department names.

   This makes it easier to generate employee directories, manage payroll data, and perform performance reviews.

Why You Should Learn VLOOKUP

Mastering VLOOKUP is crucial for anyone looking to efficiently manage and analyze data in Excel.

Understanding how to leverage this function can significantly enhance your productivity and data handling capabilities. While VLOOKUP is powerful, learning to use it effectively requires proper training and practice.

Join Our Excel Training Course

At Intellisoft Training, we offer a comprehensive Excel course designed to teach you the ins and outs of VLOOKUP, along with other advanced functions.

Our expert trainers will guide you through practical, step-by-step exercises to ensure you gain a thorough understanding of how to use VLOOKUP in real-world scenarios.

With our hands-on approach, you’ll quickly become proficient in this essential Excel function, enabling you to tackle complex data tasks with confidence and ease.

Don’t miss out on the opportunity to enhance your Excel skills. Enroll in our training course today and unlock the full potential of VLOOKUP and other powerful Excel functions.

Learn to Use VLOOKUP & HLOOKUP Functions in Excel

If you would like to learn more about becoming an expert at using the VLookup Functions in Excel, you should sign up for our Vlookup Training in Singapore.

You’ll learn a lot about Text Functions in Excel, or more advanced stuff like Pivot Tables, Macros, Advanced Sorting, Advanced Filtering, Advanced Charting.

You can attend our 3 day program, which attracts Government grants (for individual or company sponsored Singaporean or PR candidates), and leads to the industry certification.

Arrange for In-House Training for Microsoft Excel Lookup Functions

Most companies arrange for a in-house training on Excel or Photoshop certification training to train their entire staff in batches of 15-20 employees per batch. This is an excellent option to improve employee retention, increase their efficiency, and improve customer satisfaction.

Learn about the Corporate Training options, and get attractive discounts and grants when you arrange a corporate training at your office or at our training centre.

In the next article, I’ll talk about the date and time functions within Microsoft Excel.

If you would like anything else to be covered in the blog, you can let me know. Hope to see you at our Advanced Excel Training. I love to teach the Advanced Excel class, because it covers the Advanced Excel Formulas, Pivot tables, Macros, Charts, Sharing, Protection and much more. It is great to see newbies learn and discover the true potential of Excel in just 3 days.

Till then… All the Best!

Cheers,
Vinai Prakash
Founder & CEO, Intellisoft Training Pte Ltd