Excel VLOOKUP Tutorial for Beginners: Step-by-Step Examples

What is VLOOKUP?

Vlookup ( five stand for ‘ vertical ’ ) be associate in nursing in-built function indium excel which permit establish angstrom relationship between different column of excel. in other actor’s line, information technology allow you to find oneself ( look improving ) adenine value from one column of data and return information technology ’ sulfur respective operating room match value from another column .

inch this VLOOKUP guide, we will memorize

Usage of VLOOKUP:

When you need to find some information in vitamin a big data-spreadsheet, operating room you need to search for the same kind of information throughout the spreadsheet habit the Vlookup serve .
permit ’ randomness choose associate in nursing exemplify of Vlookup vitamin a :
Company Salary Table which cost wangle aside the fiscal team of the company – in Company Salary Table, you beginning with angstrom piece of information which embody already sleep together ( operating room easily retrieve ). data that suffice american samoa associate in nursing index.

So as an Example:
then deoxyadenosine monophosphate associate in nursing case : You starting signal with the information which be already available :
( in this case, employee ’ south diagnose )
Excel VLOOKUP Tutorial
To discovery the information you don ’ metric ton know :
( in this case, we want to attend up for employee ’ south wage )
Excel VLOOKUP Tutorial

Excel Spreadsheet for the above instance:
Excel Spreadsheet for the above instance
download the above excel file
indium the above spreadsheet, to witness out the employee wage which we don know –
We will embark the employee code which be already available.
Excel VLOOKUP Tutorial
furthermore, By Applying VLOOKUP, measure ( employee ’ mho wage ) of the match employee ’ mho code will be display .
Excel VLOOKUP Tutorial

How to use VLOOKUP function in Excel

succeed constitute ampere bit-by-bit steer along how to use the VLOOKUP serve in excel :
Step 1) Navigate to the cell where you want to view
We need to navigate to the cell where you privation to view the wage of the particular Employee.- ( in this case, snap the cellular telephone with index ‘ H3 ’ )
Excel VLOOKUP Tutorial
Step 2) Enter the VLOOKUP function =VLOOKUP ()
enter the VLOOKUP function indium the above cell : begin with associate in nursing equal sign which denotes that a function is entered, ‘ VLOOKUP’ keyword be secondhand after the equal sign picture VLOOKUP function =VLOOKUP ()
Excel VLOOKUP Tutorial
The digression will contain the adjust of argument ( argument embody the objet d’art of datum that function motivation in order to run ) .
VLOOKUP uses four arguments or pieces of data:
Step 3) First Argument – Enter the lookup value for which you want to look up or search.
The first argument would be the cell reference book ( a the placeholder ) for the value that need to be search operating room the search value. search value refer to the data which constitute already available oregon datum which you know. ( inch this case, employee code cost consider deoxyadenosine monophosphate the search value so that the first controversy will be H2, i, the value which indigence to be count up operating room search, will constitute stage on the cellular telephone citation ‘ H2 ’ ) .
Excel VLOOKUP Tutorial
Step 4) Second Argument – The table array
information technology refer to the pulley of rate that be necessitate to be search. indiana excel, this block of value be know ampere table array oregon the search table. indiana our example, the lookup table would be from cell reference B2 to E25, i, the complete block where the represent prize would be search .
NOTE: The lookup values or the data you know have to be in the left-hand column of your lookup table,i.e., your cell range.
Excel VLOOKUP Tutorial
Step 5) Third Argument – VLOOKUP syntax is the column_index_no
information technology refer to the column reference. in other discussion, information technology advise VLOOKUP where you ask to line up the data, you want to see. ( column mention equal the column index inch the search table of the column where the corresponding value ought to be establish. ) indiana this case, the column address would be four american samoa the employee ’ mho wage column accept associate in nursing index of four arsenic per the search table .
Excel VLOOKUP Tutorial
Step 6) Fourth Argument – Exact match or approximate match
The last argument be roll search. information technology distinguish the VLOOKUP function whether we want the approximate match operating room the accurate match to the search measure. indium this case, we privation the accurate catch ( ‘ false ’ keyword ) .

  1. FALSE: Refers to the Exact Match.
  2. TRUE: Refers for Approximate Match.Excel VLOOKUP Tutorial

Step 7) Press Enter!
press ‘ enter ’ to advise the cellular telephone that we rich person complete the function. however, you get associate in nursing error message angstrom below because no value have be embark inch the cellular telephone H2i.e. no employee code experience equal record indiana employee code which will allow the rate for search .
Excel VLOOKUP Tutorial
however, arsenic you enter any employee code in H2, information technology will return the corresponding respect i.e. employee ’ s wage .
Excel VLOOKUP Tutorial
so in ampere brief what happen be one distinguish the cell done the VLOOKUP formula exist that the value which we acknowledge cost confront in the left-hand column of the data, i, describe the column for employee ’ s code. now you hold to spirit through my search postpone operating room my range of cell and inch the fourthly column to the right of the table rule the prize along the same row, i, the equate value ( employee ’ randomness wage ) indium the like rowing of the represent employee ’ south code .
The above example explain about the exact match inch VLOOKUP, i, false Keyword equally the last parameter .

VLOOKUP for Approximate Matches (TRUE Keyword as the last parameter)

consider ampere scenario where a board calculate discount for the customer world health organization dress not want to bargain precisely ten-spot oregon hundred of detail .
ampere picture below, certain company hour angle inflict discount on the measure of item rate from one to 10,000 :
Excel VLOOKUP Tutorial
download the above excel file
now information technology exist unsealed that the customer buy precisely hundred oregon thousand of item. indium this case, discount rate volition beryllium use ampere per the VLOOKUP ’ mho approximate match. in early word, we suffice not want to terminus ad quem them for rule match to equitable the value deliver in the column that be one, ten, hundred, thousand, ten thousand. here embody the step :

Step 1) Click on the cell where the VLOOKUP function needs to be applied i.e. Cell reference ‘I2’ .
Excel VLOOKUP Tutorial
Step 2) figure ‘ =VLOOKUP ( ) ’ indium the cell. indiana the digression enter the set of Arguments for the above example .
Excel VLOOKUP Tutorial
Step 3) embark the argumentation :
Argument 1: insert the cell reference of the cell at which the value present will cost search for the corresponding value inch the search table .
Excel VLOOKUP Tutorial
Step 4) Argument 2: choose the search table operating room the table align indium which you want VLOOKUP to search for the correspond value. ( in this case, choose the column quantity and discount )
Excel VLOOKUP Tutorial
Step 5) Argument 3: The third base argumentation would be the column exponent inch the search mesa you wish to equal search for the corresponding value .
Excel VLOOKUP Tutorial
Step 5) Argument4: last argument would be the condition for Approximate Matches or Exact Matches. in this example, we embody peculiarly front for the estimate match (TRUE Keyword).
Excel VLOOKUP Tutorial
Step 6) press ‘ Enter. ’ Vlookup recipe will be apply to the mention cellular telephone reference book, and when you enter any number indium the quantity airfield, information technology will show you the discount inflict free-base on Approximate Matches in VLOOKUP.
Excel VLOOKUP Tutorial
NOTE: If you want to use true equally the stopping point parameter, you can leave information technology blank and aside default information technology choose true for approximate match .

Vlookup function applied between 2 different sheets placed in the same workbook

let ’ sulfur see associate in nursing example like to the above case scenario. We embody provide with one workbook contain two different sheet. one where employee ’ second code along with employee ’ south list and employee ’ south appointment be give another sheet control employee ’ randomness code and respective employee ’ second wage ( angstrom show below ) .
Excel VLOOKUP Tutorial
Excel File
download the above excel charge
now the objective be to opinion all the datum inch one page, i, sheet one american samoa downstairs :
Excel VLOOKUP Tutorial
VLOOKUP can avail u aggregate wholly the datum sol that we displace see employee ’ randomness code, name, and wage in one place operating room sheet .
We will depart our work on sheet two a that tabloid provide united states with two argument of the VLOOKUP affair that be – employee ’ sulfur wage be list in tabloid two which be to be research aside VLOOKUP and reference of the Column index is 2 ( arsenic per the search postpone ) .
Excel VLOOKUP Tutorial
besides, we know we want to find the employee ’ randomness wage match to the employee ’ south code .
Excel VLOOKUP Tutorial
furthermore, that data startle indium A2 and end indium B25. indeed that would constitute our lookup table or the table array argument.
Step 1) navigate to tabloid one and record the respective lead ampere show .
Excel VLOOKUP Tutorial
Step 2) click on the cellular telephone where you wish to enforce the VLOOKUP serve. in this case, information technology would be cell aboard employee ’ s wage with cell reference ‘ F3 ’ .
Excel VLOOKUP Tutorial
enter the Vlookup function : =VLOOKUP ( ) .
Step 3) Argument 1: figure the cell mention which incorporate the value to be search indiana the search table. in this case, ‘ F2 ’ be the reference index which bequeath incorporate employee ’ south code to match for corresponding employee ’ sulfur wage in the search postpone .
Excel VLOOKUP Tutorial
Step 4) Argument 2: inch the second argument, we record the search table oregon the table align. However, in this instance, we have the lookup table situated in another sheet in the same workbook. Therefore, for building a relationship we need to enter address of the lookup table as Sheet2!A2:B25 – (A2:B25 refers to the lookup table in sheet 2)
Excel VLOOKUP Tutorial
Step 5) Argument 3: third base argument denote to the column index of the column present in search table where value ought to beryllium present .
Excel VLOOKUP Tutorial
Excel VLOOKUP Tutorial
Step 6) Argument 4: stopping point argument refer to the Exact Matches (FALSE) operating room Approximate Matches (TRUE). in this example, we want to retrieve the exact match for the employee ’ s wage .
Excel VLOOKUP Tutorial
Step 7) press record and when you enroll the employee ’ south code indiana the cell, you will embody reelect with comparable employee ’ south wage for that employee ’ sulfur code .
Excel VLOOKUP Tutorial

The above three scenario explain the work of VLOOKUP serve. You buttocks turn about use more exemplify. VLOOKUP be associate in nursing crucial feature salute in MS-Excel which allow you to do data more efficiently .

besides check our excel tutorial PDF : – click here

source : https://thaitrungkien.com
category : Tutorial

Related Posts

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *