VLOOKUP

 According to Microsoft Excel VLOOKUP can be defined as a function, “that looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default the table must be sorted in an ascending order.”.

VLOOKUP is one of the most useful and important functions in Microsoft Excel. It is generally used to look up a particular value in huge data sheets where manual intervention can be cumbersome. The alphabet ‘V’ in VLOOKUP stands for “Vertical” so this function is sometimes also called vertical lookup.

The term “Vertical” signifies that it can be used to look up values vertically i.e. it can be used to look up values inside a column.

Before we understand how to use a Vertical Lookup function, we must understand what its objective is. Let’s try to understand this with a sample problem.

Suppose we have a table as shown below.

https://qphs.fs.quoracdn.net/main-qimg-3698ec47f8488e4838c9a7b4b53e276d

Objective: Our objective is to find the salary of any particular employee (say: Benjamin Mutricy) based on his name.

Solution: Now, lets try to apply a VLOOKUP to find the solution.

https://qphs.fs.quoracdn.net/main-qimg-e3a36451986f70e7b7b8bcbf05b42b3b

lookup_value: This is the value based on which the lookup is to be performed. In our case lookup_value is in the cell B4 i.e. “Benjamin Mutricy”.

https://qphs.fs.quoracdn.net/main-qimg-8fbd1199d56a5b0c237406e9f90a4252

table_array: This is the range of the table from which the values are to be fetched. Note that this ‘table_array’ should always contain ‘lookup_value’ in its leftmost column.

https://qphs.fs.quoracdn.net/main-qimg-037996e6ab8b9c978cb9e6a6261e6ac2

col_index_num: This specifies the positional reference of the column that you want the VLOOKUP to return.

https://qphs.fs.quoracdn.net/main-qimg-f08fdf78ffb934292555ebf1654922e3

range_lookup: This specifies that whether the match should be exact or approximate. FALSE specifies exact match.

https://qphs.fs.quoracdn.net/main-qimg-b153c513e90664382e09814448ba0a86

So, in this case the VLOOKUP function would be: =VLOOKUP(B4,B7:D14,3,FALSE)

 

VLOOKUP- “V” Stand for vertical LOOKUP

If you want to use VLOOKUP formula, you should aware about Vlookup info.

VLOOKUP SYNTAX

=vlookup(lookup_value,table_array_col_index_num,[range_lookup]

lookup_value :- lookup basis of common factor

table_array :- where to look / source data

col_index_no :- which column record want to fetch

[range_lookup] :- 0/False, if you want exact match or 1/True, if you want approximate data

https://qphs.fs.quoracdn.net/main-qimg-2ecbc5af57c7ded675af88c3fd6504e0-pjlq

The vlookup stands for vertical lookup.

what is the marks scored by Leo in the below table ?

https://qphs.fs.quoracdn.net/main-qimg-545cc556aa6460934340be8c7dc80868

You would have scanned the first column to find where does the name “Leo” appears and then use that to find the marks against it which is in the second column of the table, to find that leo scored 92 marks.

Vlookup works in similar fashion and comprises of 4 components.

https://qphs.fs.quoracdn.net/main-qimg-b276de0cad3b7278e1def743c7b51fed

Lookup_value : the value against which you want to find ( or look for ) another value. vlookup always assumes that this lookup value will be in somewhere in the very first column of the table.

Table_array : the total range of the table. starting from top left to bottom right.

Index_column: this refers to the column number within the above mentioned table_array in which you want to find your desired value ( against the lookup_value you selected in first step).

range_lookup: Range lookup is a boolean variable ( takes only 0 ( true ) or 1 ( false ) ). give 0 if you want to find the value against the exact lookup_value. give 1 if you are ok with approximate value. worth noticing is that range_lookup is in square brackets which means that this is an optional parameter for vlookup.

working:

https://qphs.fs.quoracdn.net/main-qimg-0b52c0c005fdbd4f2c04066b6af3b3fa

As you can see in the above image, i have a table in C and D columns, starting from cell C4 ( top left ) and ending at cell D9 ( bottom right ).

i’ve applied vlookup in cell H5. My lookup value is in cell G5 ( Leo ) .

if you now look at the formula bar ( top ) you can clearly see 4 parameters within vlookup formula separated by commas. G5 is the lookup value, C4:D9 is the table range. 2 is the column in which i want to find the value i need to find. 0 because i want exact ( because approximating marks of a student by giving the last parameter as 1 would sure as hell would be a disaster ).

Now, when you apply Vlookup in real life you might come across errors. most of the errors can be because of below mentioned mistakes ( these are from my experiences )

·         you get error despite a correct vlookup formula.
Reasons :

o    check it the lookup value has spelling mistake.

o    check if the lookup value or the column containing the lookup value has extra spaces either before or after the lookup value. You cant see spaces which might be there at the end but excel considers that and treats that as a part of lookup value.

o    if the lookup_value is a number, there can be case that the number has a different format in the table. There can also be a case where the lookup_value contains decimal point but it is approximated in the table, that will also give you an error because the excel will search the exact value. This is the situation where in you should use approximatation ( the last part of vlookup )

Let me know if this helps. ( will someday start youtube channel for excel tutorials :) ) .

Syntax =VLOOKUP (value, table, col_index, [range_lookup])

Input Arguments :

lookup_value – The lookup value, like as in example the lookup value is 148.

table_array – The value which you are looking for (It's a range of cells or a named range) like as in example (A3:D13), it will be very easy to copy the formula into other cells.

col_index – The value you want to returned from which column? like as in example (it is from 4th column)

[range_lookup] – it defaults (TRUE – approximate match) and (FALSE = exact match).

 

 

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