Friday, 12 October 2018

VLOOKUP in Google Sheet


VLOOKUP function in google sheet is used to search or looking up a data or information from a range of columns. VLOOKUP returns a specified data or information to the destination cell.

There is a common belief that the VLOOKUP function is a complicated spreadsheet formula.  Even interviewers also asking the practical questions about VLOOKUP in the interviews of candidates for almost all the departments like finance, accounts, HR etc.  But we can see that this belief is not true once you try VLOOKUP function in google sheet.



Syntax

VLOOKUP(search_key, range, index, [is_sorted])


Compulsory Parameters

Search Key: This is the value we need to search in the range ( It must be the value contained in the first column of the range).

Range: This is the selected range of columns to be searched.

Index: The column index of the value which will be returned. if the selected range is of 5 columns, 'Index' must be a value in between 1 to 5.

Optional Parameter

is_sorted: It is confirming whether the first column of the range is sorted or not. The answer may be two True or Falso.

By default, it will be True, but False is recommended as per the situation. In case, we set the value for the parameter 'in order' is 'True', only the nearest match will return to the destination cell. 

If we are using a value for 'is sort' parameter as 'False', then the Exact match will return.


How to use  VLOOKUP FUNCTION in Google Sheet with an example.

VLOOKUP is commonly applied in all the functional activities where data is using. 

Suppose we have a list of Pay Roll of a company. Pay Roll list normally contains a huge number of data and details of information with regards to salary, allowance and other tax related items of employees. Here we are using some information on a payroll for our example.

Step 1: Arrange the table in a meaningful way

As the VLOOKUP looks a Serch Key and its corresponding value from left to right, the table must be arranged in such a way that the unique search key column is placed in the left side of the array. 

Refer to the table shown below, containing employee data (not full) in a table. 



Step 2: Using the VLOOKUP Function in Google Sheet

Suppose, we need to find Basic Salary of some employees, it is difficult to find the exact data from a table containing the huge number of employees.

And also we observed that, practically, VLOOKUP function requires the data to fill some other table in a different sheet. So we need to apply a formula to find the required data based on the 'search key'.



Here we applied the formula as

 =VLOOKUP(I2,$A$2:$G$14,5,FALSE)

For clarification, we can analyse what the VLOOKUP formula asking Google sheet.

I2 - means the 'search key ' for the value contained in the particular cell of 'I2'.

$A$2:$G$14 - Representing a range in which the lookup value need to be searched. The important process is to lock the range, otherwise, when we drag the formula, it may give us the wrong information or data.

To lock, after selecting the range, press 'F4' key from your keyboard or enter '&' sign in front of cell address separately.

Eg. ($A$2:$G$14 - Locked, A2:G14 ) Not locked

This is the common mistake found in the application of the VLOOKUP Formula.

5 - It is asking to return an answer corresponding to the 'search key ' from this column. When we count the column number, ensure that the actual columns are counted

FALSE - This is indicating that the 'search column' is unsorted.

Once we apply the formula, it will return the values as given below





In the above table, we can be noticed that two rows giving '#N/A' results. It means the search keys of these two cells not contained in the array. 

In this case, the VLOOKUP formula returns an error message. To avoid this, we can use an IFERROR formula in addition to this VLOOKUP formula

=IFERROR(VLOOKUP(I8,$A$2:$G$14,5,FALSE),"Not Found")

After changing the formula, we can see the table as given below



Using a VLOOKUP formula in different sheet

If you are using VLOOKUP  function in a different sheet, the formula structure will be as given below

=VLOOKUP(B4,'VLOOK UP'!$A$2:$G$14,5,false)

But here we need to select the source file by clicking after entering the LOOKUP formula.




VLOOKUP function is greatly used in the financial analysis and dynamic templates of financial modellings. This function can help to select the appropriate interest rate, inflation rate, depreciation rates based on the situations predicted by the Financial analyst.

Now you can understand how to use VLOOKUP Function in Google sheet. Thank you for reading and hope to see you later.

For any clarification, please don't hesitate to comment below. Share it with social media if you like it.

Qatar Airways