Sanusi A. L. - Excel Vlookup: A Step by Step Visual Guide

EXCEL

VLOOKUP

A Step by Step Visual Guide

By

Sanusi A. L.

EXCEL VLOOKUP

Copyright Sanusi A. L.

ISBN: 9791220837439

Published in the United States

Microsoft and Excel are registered trademarks of Microsoft Corporation. The author or publisher of this book is in no way associated with any product or vendor mentioned in this book.

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews. Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. The author/publisher, its dealers and distributors will not be held liable for any damages caused or alleged to be caused directly or indirectly by this book. The author/publisher has endeavored to provide trademark information about all of the companies and products mentioned in this book. However, he cannot guarantee the accuracy of this information.

Table of Contents

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 Vlookup .

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):

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

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 returns the value in the first column in table_array. Another example: A col_index_num argument of returns the value in the second column in table_array. And so on! It is a required argument.

Range_lookup (Also known simply as theRange): 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 () 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 () be used in most cases. It is an optional argument.

Important Notes :

1. 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.
2. 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.
3. 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.

Fig. 1.0 : Excel table for Quiz 1

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:

Lookup_value: E3.

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: