Excel – VLOOKUP Function along with Hindi Video

VLOOKUP Function:

Summary

VLOOKUP is one of the most powerful tools in Excel. The VLOOKUP function enables you to look up data corresponding with the row of a lookup value and corresponding column index. It sounds like a very simple thing, and it is.

For example, the VLOOKUP function can use the relational data essentials of two separate tables and affix the data from one table to the other table.

You can create a simple Excel table that uses VLOOKUP to lookup and present data from one or other tables.

 Don’t worry if it’s sound little difficult, by the end of this article, you will be able to teach others about the VLOOKUP.

 First will let you know about the purpose and syntax of the same.

 

Purpose

The VLOOKUP function can use the relational data essentials of two separate tables and affix the data from one table to the other table.

 

Arguments & Syntax

 

As Syntax given above VLOOKUP contain 4 different arguments, the meaning of the same is given below.

Arguments	Meaning
lookup_value	The value available in the first column of the source table (table_array) or can say the value to look for in the first column of the specified range from which retrieving value.
table_array	The table specified as a range from which to retrieve the value.
col_index_num	The value column from which retrieving data.
[range_lookup])	The same is option TRUE is approximate match and FALSE is an exact match

Translation

 So now we will start using a sample broking client investment data table.

This table has been organized and separated into two different worksheets and each worksheet containing a different table order.

Table containing Investor name, Account number, Investment scheme but investment amount is not there.

In the second sheet, we are having an Investor Name, Account Number, and Bank Balance. Investor name can be different in bank and broking house or there can be a mistake done by anyone of them so we will use Account number as a lookup value.

 

Formula Entered Above =VLOOKUP(B2,'Bank Balance'!$B$1:$C$14,2,0)

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

Sr. No.	Argument	Value
1	lookup_value	B2
2	table_array	'Bank Balance'!$B$1:$C$14
3	col_index_num	2
4	[range_lookup])	0 or FALSE

 If in the above formula value in “B2” is common in the array table (Source data) and lookup value. Need to select ‘Bank Balance’$B$1:$C$14 where the “B” column contains the same the value available in “B2” and thereafter mention returned column number as 2, so the return value is bank balance (847697).

Click here to download the excel file or open with Google sheet.

Click here to open ASHVI CITY – Mr. Excel youtube video or click on the start button to view caption video.

Function Return Value

The value entered by you for TRUE or FALSE.

Functions Used

=VLOOKUP(B2,'Bank Balance'!$B$1:$C$14,2,0)

 

Evaluation Operator	Meaning
=	Equal To
$	To block the selected cell or range

 

Common Difficulties:

ERROR	Meaning of Error
#NAME? in cell	This usually means that the formula is misspelled.
#REF! in cell	This usually means that something is missing in formula or column no. selection is greater than the actually selected array table (source data).

 Important Notes:

1. The important thing to remember when using VLOOKUP: your data has to be organized in columns,
2. Selection of the source data need to start from common value,
3. Before Copy and Paste use dollar sign. ($) to block the selection range and
4. Start counting for “col_index_num” from the range selected from “table_array” instead of sheet column.

Are you having any Question or Comment? Hit me!