Vlookup Formula in Excel with Example Between Two Sheets

How to use VLOOKUP Formula with Examples

In the VLOOKUP Formula V Stands for Vertical Lookup Means to find the specific value (defined in the Formula) in the whole vertical specified column.VLOOKUP Formula is the best formula in the excel 2007 and higher version of the Microsoft office product 2007,2009,2013,2016 and the higher version of the excel. it is also present in the Google Sheet.

How-to-use-VLOOKUp-formula-in-excel-thumbnail

In the simple language we can say the VLOOKUP  Formula says us:

=VLOOKUP( which value you want to lookup, where want to lookup for it, the column number in the range containing the value to return, return an approximate or exact match- Indicated as 1 / TRUE, or / False).

In Technical Language 

Use VLOOKUP Formula to lookup up a value in a table.

VLOOKUP Formula SYNTAX

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

What are the 4 arguments you give in VLOOKUP?

Argument
Detail
Description
lookup_value    (required) The value you would like to lookup. the worth you would like to lookup must be
within the first column of the range of cells, you specify within the
table_array argument.
For
example, if table-array spans cells B2:D7, then your lookup_value must be in
column B.
Lookup_value
are often a value or a reference to a cell.
table_array    (required) The range of cells within which the
VLOOKUP will look for the lookup_value and also the return value. you can use
a named range or a table, and you can use names within the argument instead
of cell references.
 The first column within the cell range must contain the lookup_value. The cell range also must include the return value you wish to search out.
 Col_index_num    (required)
The column number (starting with 1 for the left-most column of table_array) contains the return value.
range_lookup   (optional) A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match:

Approximate match – 1/TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don’t specify one. For example, =VLOOKUP(90,A1:B100,2,TRUE).

Exact match – 0/FALSE searches for the exact value in the first column. For example, =VLOOKUP(“Smith”,A1:B100,2,FALSE).

How to use VLOOKUP Formula with Example 1

You can download the practice file and do practice side by side. File Link is Given at last of this post.👇🏻👇🏻👇🏻

Suppose you have given the data set as shown below:

Data Set

How-to-use-Vlookup-formula-in-excel

and you want to search product name using the Product ID given in the column A.

Then How will you use this vlookup formula?

As in the formula basically 4 arguments are there.

lookup_value – the value we have to lookup from the data set. As we have to  lookup product id 3 given in the cell C2.

table_array- under this we have to pass the data table which is in this case given from range $C$5:$E$9.

col_index_num- in this argument we should to pass the column number we need as required value. Here we need product name as columns  2

range_lookup – we need  exact match of product ID  and its corresponding product name. so we pass the either 0 or FALSE. to get exact match.

So the formula will be

=VLOOKUP(C2,$C$5:$E$9,2,0)

Result Comes out to be  ORANGE because product ID is assign to ORANGE.

If you want to the Price of this product just change the column index number with 3. then your required result will come out to be $0.75

How to use VLOOKUP Formula with Example 2

If you want to search Price of the Orange Product then also you can use VLOOKUP formula in EXCEL. As Shown in Screenshot given below.You can pass lookup value in the inverted commas as shown.

How-to-use-Vlookup-formula-in-excel-Example-2

  • lookup_value – the value we have to lookup from the data setwe can pass on in the Inverted Coma
  • able_array- under this we have to pass the data table which is in this case given from range $D$6:$E$10.Note: the column from which you want to search it must be very first columns of selected array
    • col_index_num- in this argument we should to pass the column number we need as required value. Here we need product name as columns  2range_lookup – we need  exact match of product ID  and its corresponding product name. so we pass the either 0 or FALSE. to get exact match.So the formula will be

      =VLOOKUP(“Orange”,$D$6:$E$10,2,0)

      How to use VLOOKUP Formula in Excel from different Sheets Example 3

  • Here is the data given on the sheet 1
  • First of all add the value in cell A3 which you want to search in data column given on the sheet 1.
  • Then start adding the formula vlookup in the cell B3.
  • lookup_value – the value we have to lookup from the data set. As we have to  lookup product id 2 given in the cell A3.Table_array- Now go to sheet 1 and selected the range C3:D7 Col_index_num- Here we need product price as columns  3.Range_lookup – we need  exact match of product ID  and its corresponding product price. so we pass the either 0 or FALSE. to get exact match.Here is the formula used on the sheet 2
  • Formula will be =VLOOKUP(A3,Sheet1!C3:D7,2,0)
I hope you understood the concept How to use Vlookup formula in Excel. But if Still you have any query you can ask it in comment section. i will reply as soon as possible.

Practice File Link: How to use VLOOKUP FORMULA in Excel File

Leave a comment

PLAYER RATING | PARIS SAINT-GERMAIN PSG Unveiling the Mysteries: Can Mass be Converted into Energy? Can Mass Be Negative?