Learn Excel Vlookup Exact Match
Practice Projects & Solutions Included for Beginners
Formats: Pdf & Epub.
Guarantee: 45 days.
Download Ebook: Download here.
Order Paperback: Contact us.
When you need to find information in a large spreadsheet, or if you are always looking for the same kind of information, then you need to use the Excel vlookup function. Excel vlookup exact match works a lot like a phone book. Excel Lookup functions in general are used to look up and extract data from a list or table and insert it into another list or table.
It is widely agreed that close to 60 percent of Excel users leave 80 percent of Excel untouched. That is, most users do not tap into the full potential of Excel’s built-in utilities. Of these utilities, one of the most prolific by far is the Excel Vlookup. Despite the fact that pivot tables have been a cornerstone of Excel for more than 18 years, Vlookup remains one of the most underutilized tools in the entire Microsoft Office Suite.
Having found this book, you are savvy enough to have heard of Excel Vlookup or even have used them on occasion. You have a sense that Vlookup has some power that you are not using, and you want to learn how to leverage that power to increase your productivity quickly.
With only this book, you will be able to increase your productivity, and produce reports in minutes instead of hours.
Table of Contents
How to Use This Book
1. What Are Excel Lookup Functions?
- Fuzzy Lookup
- Syntax of Vlookup Function
- Quiz 1: Using A Vlookup Function
2. Using Vlookup to Search Within A Large Volume of Data
- Quiz 2: Using A Vlookup Function to Save Time
3. Vlookup Error Messages
- The Notorious #N/A error
- The #REF Error
4. The MATCH Function
- The INDEX Function
- Combining MATCH and INDEX Functions
- Summary of What the MATCH Function Does
- Quiz 3: How to Use the INDEX Function Like A Pro
- Quick Excel Trick to Add Some Automation to your INDEX Lookup
5. How to Apply VLOOKUP in VBA
- The Syntax of VBA VLOOKUP
- Quiz 4: Use Vlookup in VBA to Find Justin Jones’ Salary from an Employee Table
- Quiz 5: Write A Code That Adds the Department field to the Employee Table
- Quiz 6: Write A Code That Displays Details of an Employee from the Employee Table When the
- Employee ID is Entered
6. Advanced Vlookup Tips, Tricks and Techniques
This book can be used as a tutorial or quick reference guide. It is intended for users who are comfortable with the basics of Microsoft® Excel® and are now ready to build upon this skill by learning Excel Vlookup Exact Match function.
This book assumes you already know how to create, open, save, and modify an Excel® workbook and have a general familiarity with the Excel® toolbar (Ribbon).
Most of the examples in this book use Microsoft Excel 2016. However, the functionality and formulas can be applied with Microsoft Excel version 2013. Although the screenshots in this book use Microsoft Excel 2016, functionality and display are not very much different if you are using Excel 2013.
Please always back-up your work and save often as we go. A good best practice when attempting any new functionality is to create a copy of the original spreadsheet and implement your changes on the copied spreadsheet. Should anything go wrong, you then have the original spreadsheet to fall back on.
The worksheets (exercise/quiz files) we will use in this book are available for download at the following website: https://bit.ly/383JN6h.
Excel Lookup functions are used to look up and extract data from a list or table and insert the data into another list or table. How you use the appropriate lookup function depends on how you organize your data.
There are different types of lookup functions. They are explained below. However, Vlookup is the most commonly used of all, and is the one we will focus on in this book.
Use HLOOKUP, or horizontal lookup, when the lookup values are contained in the first row of a table, and the values to be returned are in the same column but in a different row that you specify.
Fuzzy Lookup is an Excel Add-In tool developed by Microsoft to be used for comparing textual data that is not an exact match, also referred to as “fuzzy matching”.
Excel Vlookup is a useful data analysis function for comparing data in two spreadsheets when the lookup values are contained in the first column of a lookup table. It can be used to import data from one spreadsheet to another for comparison purposes. Once you have become familiar with Vlookup, you will find it a valuable tool to use for data analysis.
The formula for Vlookup looks like this (shown with a different font for readability):
As you can see, a properly formed Vlookup function has four parts, separated by commas. No spaces are allowed. These parts are also known as arguments (data that is passed to the function). Each of the four arguments are explained briefly below. We will look at them in detail later.
Lookup_value: This is the value you want to search or look up in the first column of a table or range. It is a required argument.
Table_array: This is the range of cells where you want to look up the comparison data. The values in the first column of the table_array are the values searched by the lookup_value. These can be text, numbers or logical values and are not case sensitive. It is a required argument.
Col_index_num (Meaning Column Index Number): It is the number of the column in the table_array from which the matching value must be returned. For example, a col_index_num argument of 1 returns the value in the first column in table_array. Another example: A col_index_num argument of 2 returns the value in the second column in table_array. And so on! It is a required argument.
Range_lookup (Also known simply as the Range): A logical value specifying whether you want VLOOKUP to find an exact match (FALSE) or an approximate match (TRUE). If it is omitted, it defaults to TRUE (1) for an approximate match, meaning that if an exact match is not found, the next highest value in a range will be returned. It is recommended that FALSE (0) be used in most cases. It is an optional argument.
- When searching text values, make sure the data in the first column of table_array does not contain any trailing or leading spaces, inconsistent use of straight and curly quotation marks, or non-printing characters. Otherwise, VLOOKUP could return an incorrect or unexpected value.
- When searching number or date values, make sure data in the first column of table_array is not stored as text values. Otherwise, VLOOKUP could return an incorrect or unexpected value.
- If the lookup_value is not found in the first column of the table_array, then the “#N/A” error value is returned.
To know how the VLOOKUP function works, use your Excel application to open the “Quiz 1” worksheet in your exercise folder to solve this quiz:
Consider Fig. 1.0, the worksheet which shows the Excel table with its headers in row 2 and the first column in column B of the worksheet.
In Fig. 1.0, use the VLOOKUP function to look for the name of a customer whose CustomerID is CU03.
The first thing to do is go to your worksheet and prepare the second table where your search result will be displayed. Make sure you enter your Lookup_value (CU03) in cell E3.
Therefore, the 4 arguments of Vlookup as stated as follows:
Table_array: B3:C6 (This is the range of cells where you want to perform your search. It starts from cell B3 and ends at cell C6). Check it out in Fig. 1.1.
Col_index_num: 2 (since we are sure the result we are looking for is in the column 2 of the table).
Range_lookup: FALSE (using the recommended logical value of FALSE (0) to find an exact match).
Therefore, the Vlookup formula is: