What is VLOOKUP in Excel

What is VLOOKUP in Excel and How to use it?

Published on Nov 18,20191.8K Views
Share
image not found!image not found!image not found!image not found!

 
 What is VLOOKUP in Excel and How to use it? 

In this data-driven world, one needs various tools in order to manage data. Data in real-time is huge and fetching details regarding some particular piece of data would definitely be a tiring task but with VLOOKUP in Excel, this task can be achieved with a single line of command. In this article, you will be learning about one of the important Excel functions i.e the VLOOKUP Function.

Before moving on, let’s take a quick look at all the topics that are discussed over here:

What is VLOOKUP in Excel?


In Excel, VLOOKUP is a built-in function that is used to lookup and fetch specific data from an excel sheet. V stands for Vertical and in order to use the VLOOKUP function in Excel, the data must be arranged vertically. This function comes in very handy when you have a huge amount of data and would be practically impossible to manually search for some specific data.

How does it work?

The VLOOKUP function takes a value i.e the lookup value and starts to search for it in the leftmost column. When the first occurrence of the lookup value is found, it starts to move right in that row and returns a value from the column that you specify. This function can be used to return both exact as well as approximate matches (The default match is an approximate match).

Syntax:

The syntax of this function is as follows:

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

where,

  • lookup_value is the value to be looked out for in the first column of the given table
  • table_index is the table from which the data is to be fetched
  • col_index_num is the column from which the value is to be fetched
  • range_lookup is a logical value that determines if the lookup value should be a perfect match or an approximate match (TRUE will find the closest match; FALSE checks for exact match)

Exact Match:

When you want the VLOOKUP function to look for an exact match of the lookup value, you will have to set the range_lookup value to FALSE. Take a look at the following example which is a table consisting of employee details:

exact match-VLOOKUP in Excel-Edureka

 

In case you want to look for the designation of any of these employees, you can do as follows:

  • Select the cell where you want to display the output and then type a “=” sign
  • Use the VLOOKUP function and supply the lookup_value (Here, it will be the employee ID)
  • Then pass in the other parameters i.e the table_array, col_index_num and set the range_lookup value to FALSE
  • Therefore, the function and its parameters will be: =VLOOKUP(104, A1: D8, 3, FALSE)

The VLOOKUP function starts to look for the employee ID 104 and then moves towards the right in the row where the value is found. It goes on till the col_index_num and returns the value present at that position.

Approximate Match:

This feature of the VLOOKUP function allows you to retrieve values even when you do not have an exact match for the loopup_value. As mentioned earlier, in order to make VLOOKUP look for an approximate match, you will need to set range_lookup value to TRUE. Take a look at the following example where the marks are mapped along with their grades and the class to which they belong.

Approximate match-VLOOKUP in Excel_edureka

 

  • Just like how you did for an exact match, follow the same steps
  • In place of the range_lookup value, use TRUE instead of FALSE
  • Therefore, the function along with its parameters will be: =VLOOKUP(55, A12: C15, 3, TRUE)

In a table that is sorted in the ascending order, VLOOKUP starts to look for an approximate match and stops at the next largest value that is smaller than the lookup value that you have entered. It then moves right in that row and returns the value from the specified column. In the above example, the lookup value is 55 and the next largest lookup value in the first column is 40. Therefore, the output is Second Class.

First Match:

In case you have a table that consists of multiple lookup values, VLOOKUP stops at the first match of it and retrieves a value from that row in the specified column.

Take a look at the image below:

First Match VLOOKUP-Edureka

 

The ID 105 is repeated and when the lookup value is specified as 105, VLOOKUP has returned the value from the row that has the first occurrence of the lookup value.

Case Sensitivity:

VLOOKUP function is case insensitive. In case you have a lookup value that in upper case and the value present in the table is small, VLOOKUP will still fetch the value from the row in which the value is present. Take a look at the image below:

Case sensitivity VLOOKUP-Edureka

Course Curriculum

MSBI Certification Training Course

  • Instructor-led Sessions
  • Real-life Case Studies
  • Assignments
  • Lifetime Access

 

As you can see, the value that I have specified as a parameter is “RAFA” whereas the value present in the table is “Rafa” but VLOOKUP has still returned the specified value. If you have an exact match even with the case, VLOOKUP will still return the first match of the lookup value irrespective of the case used. Take a look at the image below:

Case sensitivity VLOOKUP-Edureka

Errors:

It is natural to encounter errors whenever we make use of functions. Similarly, you can encounter errors when using VLOOKUP function as well and some of the common errors are:

  • #NAME
  • #N/A
  • #REF
  • #VALUE

#NAME Error:

This error basically is to inform you that you have made some mistake in the syntax. To avoid syntactical errors, it’s better to use the Function Wizard provided by Excel for every function. The Function Wizard helps you with information regarding every parameter and the type of values that you need to enter. Take a look at the image below:

function wizard Excel-Edureka

 

As you can see, the Function Wizard informs you to enter any type of value in place of the lookup_value parameter and also gives a brief description of the same. Similarly, when you select the other parameters, you will see information regarding them as well.

#N/A Error:

This error is returned in case no match is found for the given lookup value. For example, if I enter “AFA” instead of “RAFA”, I will get #N/A error.

Error vlookup-Edureka

 

In order to define some error message for the above two errors, you can make use of the IFNA function. For example:

IFNA VLOOKUP-Edureka

 

#REF Error:

This error is encountered when you give reference to a column that is not available in the table.

ref error-Edureka

 

#VALUE Error:

This error is encountered when you place wrong values to the parameters or miss some compulsory parameters.

value error-Edureka

 

Two-way Lookup:

Two-way lookup refers to fetching a value from a two-dimensional table from any cell of the referenced table. In order to perform a two-way lookup using VLOOKUP, you will need to use the MATCH function along with it.

Comments

Post a Comment

Popular posts from this blog

Vyapar App’s Complete Feature List

Image
  Vyapar App’s Complete Feature List: All About the BEST Billing Software for Small Businesses in India by  Vyapar November 22, 2019 Vyapar is a GST Business Accounting Software .  With Vyapar App, you can  make & share Invoices on WhatsApp, manage stocks/Inventory ,  make estimate bills, generate GSTR reports, t rack unpaid invoices, send payment reminders and collect payments directly online using UPI payments.  It has other features like business expense tracking , daily sale purchase record, shows profit and loss report. Here’s how this GST Billing App looks like: The main left menu in the Vyapar App has the following options:  Business Dashboard, Reports, Sale, Purchase, Expense, Other Income, Cash & bank, My Online Store, Settings, Backup/Restore, Utilities, Rewards, Help & Support . By tapping the top-left corner of the App (blue background), you can add your  company details  like name, company phone number, company addres...
Read more

Zoho Books Welcome Guide

Image
  Zoho Books Welcome Guide Navigating Zoho Books We know you're in a hurry! To make it easier for you, we've put together the shortest route to navigate your way around Zoho Books. Before we get into the specifics, see where you'll find what. 1.  The  Navigation Panel  on the left-hand side lists all the modules of Zoho Books. 2.  Get into the  Sales  tab and the  Purchases  tab to manage Estimates, Invoices, Expenses, Bills, and more. 3.  The  Quick Create  button by the respective modules is the shortcut to create new transactions. 4.  The  Quick Create  option can be used to create a transaction on the fly from anywhere in Zoho Books. 5.  Clicking on the  Recent Activities  option will display of list of most recent transactions you have carried out in your organization. 6.  Use the  Search  bar to quickly find a specific transaction, contact, item or more. 7.  The  Noti...
Read more

VLOOKUP function

Image
 VLOOKUP function Show All Hide All This article describes the formula syntax and usage of the VLOOKUP function  (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel. Description You can use the VLOOKUP function to search the first column of a range  (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees' ID numbers are stored in the first column of the range, as shown in the following illustration. If you know the employee's ID number, you can use the VLOOKUP function to return either the department or the name of that employee. To...
Read more