As youuse Microsoft Excelmore and more , you ’ll find that the VLOOKUP function is a very pop peter for dealing with large Excel directories or database . It allows the user to quickly find targeted data about a specific ingress without needing to face through the entire spreadsheet .
Do n’t worry , though — this function is n’t well-nigh as intimidating as it look , and it can save a mountain of fourth dimension and permit for more freestyle analysis . Here ’s how to apply VLOOKUP in Excel .
Understanding the VLOOKUP pathing
The VLOOKUP map is divided into four unlike “ arguments , ” or value input into your social function . These delimitate exactly where VLOOKUP will overstretch information from , so while you start the function with the basic = VLOOKUP ( ) , the four arguments that you put in those divagation will be doing all the work .
In brief , you ’ll be telling VLOOKUP the economic value you want to look up , the range where the note value is located , the column where the return value is , and if the homecoming ask to be accurate or close together . If you do n’t have a bunch of experience with Excel function , that may not make much signified . allow ’s make it easier by break up each argument down into how it performs . Think of an example like an employee directory or a class grade tack to see how this can work in real life .
stair 1 : Select the first disputation .
This is your lookup value , or the identifying entropy that you will use to pull information about one specific transmission line in a database or directory . This is the space where you will be inputting information such as employee or family IDs , specific public figure , and so on . you’re able to choose where this lookup economic value goes , but ideally , it will be tight to the VLOOKUP for easy psychoanalysis and understandably pronounce so you will always make love what to input .
Step 2 : Select the second argument .
This is the range where your first argument , the lookup value , is located in the range . For example , if you are looking up a specific employee ID number , then this literary argument should contain the entire database . It ’s simplest to manually click the very first entry and then drag your pointer all the way down to the concluding bottom - right wing entry at the destruction so it encompasses all the cells , holding all the values in the database . For very large databases , manually input the first entry ( A2 , for example ) , a Costa Rican colon , and the last debut ( B5 , in this case ) , like : A2 : B5 .
Note that the second argument should always start with the first ( leftmost ) newspaper column in the database or ambit . This is also why VLOOKUP wo n’t work well with horizontally orient lists , but that ’s rarefied in spreadsheets .
footstep 3 : Select the third contention .
VLOOKUP now knows the full range of the database or board where it ’s seem for information , but it needs a little more assistance . Now you need to pick out the column where the return value is located – aka the specific entry that you need when typing in your lookup note value .
The third argument needs to be a number , not a column letter . Start counting from the first entry pillar on the list and matter over to the rightfield until you reach the column with the information that you ’re interested in ( like employee bonuses or student grades ) . Input this number into the routine so that VLOOKUP eff what to reelect .
footmark 4 : pick out the fourth argument .
The 4th argument is a number dissimilar : you’re able to typecast FALSE or TRUE here to specify if you require to regress either an exact match or an approximate match . You do n’t have to do this footprint if you want to end the role here , but it does have its exercise . A FALSE argument will return an error if it can not line up your input signal value – if , for example , the employee ID you enrol does n’t be . A TRUE remark will round to the closest possible result and return the hope note value for that entry , which can simplify certain kinds of analysis .
With your VLOOKUP office complete , you could now set forth entering value in your lookup space and see the results that VLOOKUP returns .
Important notes to remember
VLOOKUP always paths to the rightfield . It will not path left . Keep this in head when arranging your lookup data .
Data ranges are interrupt when youadd a new column in Excel , so again , consider this when making changes .
VLOOKUP does n’t translate duplicates . For example , if two employee have the same last name , VLOOKUP will simply stop at the first one on the inclination , regardless if it ’s the name you wanted or not . That ’s why the procedure is often used with full name calling or ID figure instead .
VLOOKUP is case sensitive , so it can tell the difference of opinion between look up a capitalized word and one that is n’t .
Like other Excel functions , it ’s comfortable to expand VLOOKUP into a full table to return multiple values at once , look on your project . Once you ’re well-fixed with the procedure , you could start out using it in more complex ways !
Microsoft Excel affords considerable control over the data your store in it . VLOOKUP is a great way to find and rejoin data , which can then be presented in various other fashion . You probably knowhow to create a graph in Excel , but do you knowhow to create a pivot table ?
