Skip to main content

New Article

How to SUM by matching partial text in Excel

Post a Comment

VLookup function in Excel

This Vlookup function is also known as Vertical Lookup. It is commonly used in Excel to match a lookup value from a column, and returned value from another column where lookup value matched. The match type can be define as Exactly Match or Approximate Match (Ascending Order). It works like an English L shape as I think. This function has a lot of uses. It supports Array, so that you can customize the lookup values.


Vlookup Function:
VLookup function is a lookup function that used in Microsoft Excel for returning a specific data from a data table by matching a cell value (Number, Date, Text, etc.). More clearly, when you need to add an information of each record of a table from source table, by matching each cell value of a specific range with source data table, then you need to use VLookup function.

At present in Bangladesh, many company asks about VLookup in their Job Interview. Why? Because they feel that, if you do not have knowledge about VLookup (which is one of the most important function in Professional Life) then you can't survive in their company. And the company has no enough time to teach you on the Job. So, it is better that, you learn it and apply it in your daily life, just for practicing. I'm sure you will be a successful person in Professional Life. The structure of this function is:

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


Function Details:

There are 4 arguments or parameters in this function. The first 3 argument or parameters are essential. That means if you want to use VLookup function, then you must mention the first 3 parameters. And the last parameter is optional. You can ignore it if you wish. But for a better result you need to set the 4th parameter. All of these parameters are briefed here:

lookup_value: This is the value or cell reference the VLookup will find and match.

table_array: The table array where the lookup value is present. Note that, lookup_value looks for exactly what you have given and must be in the first column of your table_array. If the lookup_value is in 2nd column and you selected table_array from 1st column, then it will returns an #N/A error.

col_index_number: If the lookup_value matched in the first column of your table_array, then which column value you wish to return need to mention here.

[range_lookup]: This is an optional parameters. If you wish to look for exact match then, use FALSE and if approximate match require then use TRUE.


Example:

Assume that, A2:C4 is a data table contains the Player, Score and Country. Write a VLookup formula in B8 cell in to find out the Country by matching Players name.


Image 1: Sample data


Solution:

Assume that, Players name already entered in a cell B7 and based on this B7 cell value the Country name will show in B8 cell.

Due to we want to return automatic result in B8, that is why we need to input formula on B8 cell. Go to B8 cell and type the below formula:

=VLOOKUP(B7,A2:C4,3,FALSE)

Image 2: Example of VLookup()


How we get the result?

B7: In Vlookup function, we have used B7 for lookup_value cell. We can use "Neymar" (with quotes) as lookup_value but it would be great if we use reference every time, and we are not typing our formula for every changed in B7 cell. So, we have used B7 as cell reference.

A2:C4: It is the data table. One point here I would like to mention that, lookup_value is in the left side of returned value Brazil. Because VLookup always returns the column number which is in right side after matched column.

3: We use this column number to return the value from third column Country based on our selected table range.

FALSE: As we want to match the value exactly what have entered, that is why FALSE has selected.

Image 3: Vlookup result returned Brazil based on Neymar lookup value


Rules:

a) Returned value Brazil must be in right side column of lookup_value Neymar.
b) Returned value Brazil must be in the first column of selected data range A2:C4.



---------------------------------------------
SUBSCRIBE our YouTube Channel
---------------------------------------------
Labels:

Popular posts from this blog

How to display an image in worksheet based on a List or based on IF condition?

Excel can show image on worksheet based on a specific IF condition. So, how to do it? Simple follow the below steps: Step by Step: Step 1: Insert images in your Excel Worksheet. Here I've inserted 5 different types of balls, Football, Cricket, Pool, Basketball, Tennis ball. Note that, All balls are placed into different cell. These are placed in Picture sheet. Image 1: 5 balls placed in 5 different cells and covered photo's wide and height Step 2: In the report sheet, design the report as you wish. I've designed in my way like below: Image 2: Kids asking to Donald Duck, which ball need to throw now Step 3: Make a drop down list "Games" in E6 cell in Report sheet from Data Validation. which is as below: Football Cricket Pool Basketball Tennis You can do an IF function here in E6 in Report sheet, which will meet a certain condition and returned Football, Cricket, Pool, Basketball or Tennis. Step 4: Now the tricky part is
Read more

Fiverr Microsoft Excel 2016 Skill Test and Answers 2021

This video will help you about preparing Fiverr Excel Skill Test. I've just taken this Fiverr Excel Skill Test and successfully passed with a scored 6 out of 10. I'm sharing my Fiverr Excel Skill Test and Answers 2021 video so that you can prepare yourself if you would like to give a test on Fiverr.    I have taken the Fiverr Excel Skill Test on Fiverr and obtained 6 out of 10 scores. If you take a test and pass, it will greatly help the buyer to trust you and your skills. Please note that your exam will not be exactly the same as mine because there are many more questions in the pool than 40 which appeared in my test. But I am hopeful that you will succeed in it. If you have any queries, please write them in the comment section. I will try my best to guide you.Follow this video and try to get a general idea. By following the instructions, you can successfully pass the test.    After passing the Fiverr Excel Skill Test, Fiverr shared a Excel Expert Badge on my Profile, so that
2 comments
Read more

Cumulative Closing Balance like a Bank Statement in PivotTable

A Bank Employee in many case needs to calculate the Closing Balance after each transaction in PivotTable like a Bank Statement . But with the help of Calculated Field of a PivotTable , you can only calculate Field with Field . It's not possible to use a formula in Calculated Field where you can mention a single cell with Relative Reference Cell . Because a PivotTable acts like a Table format where you can work with Field or Column Name . A Helping Column (A regular column, that helps to get a partial result where formula applied) can be a good idea. But there is an option in PivotTable by which you can Calculate Cumulative . In this article I will show you, how you can do it. Calculating with Formula: Assume that, you have an Excel file with Debit and Credit transaction of an Account . Image 1: Sample Bank Statement In a Bank Statement , Month wise Cumulative Balance is important. In the Excel sheet, as above, it is very easy to use a formula and calc
Read more