VLOOKUP tutorial -: In this guide I will explain you fundamentals of VLOOKUP excel example. It is basic guide that make the learning more simple to inexperienced excel user. We explore how to use VLOOKUP with VLOOKUP example in excel and syntax of VLOOKUP function. Before you start, check out our excel formula with examples.
- What is VLOOKUP function?
- How to insert Excel VLOOKUP formula
- Purpose of VLOOKUP formula in excel 2013.
- VLOOKUP Syntax.
- How to use VLOOKUP formula with Youtube video
- VLOOKUP Error
- Excel VLOOKUP examples
- How to do a VLOOKUP between two spreadsheets.
- How to use VLOOKUP formula for multiple worksheets.
- VLOOKUP examples based on a single criteria.
- How to use VLOOKUP formula on a multiple lookup value.
- Excel VLOOKUP – important notes.
What is VLOOKUP function?
Basically, VLOOKUP is one of the excel formula that use to search for specific information in large data set. As per name suggest VLOOKUP- Vertically looking specific value. Suppose, if you have a list of student and marks, you could search for the marks of a specific student.
How to insert VLOOKUP formula in excel sheet.
VLOOKUP is normal excel formula, you can use as any common excel function. To more details check my previous post Microsoft excel formula list with example.It is simple formula available in all version of excel spread sheet. It is not as tuff as VBA programming. VLOOKUP simple but effective function that help for data mining, just enter = in cell and start VLOOKUP Function.
Purpose of VLOOKUP formula
The main purpose of VLOOKUP is vertically search a value in a table and produce resultant cell value. In short VLOOKUP check matching value within selected table and return matched value.
As every excel formula, VLOOKUP stated with equal to sign (=). Complete syntax for VLOOKUP formula is shown below. I will explain you each and every argument in this VLOOKUP tutorial.
Syntax: =VLOOKUP (value, table, col_index, [range_lookup])
How to use VLOOKUP formula?
VLOOKUP start with equal to sign (=), then VLOOKUP and open bracket. You can add arguments with in bracket. This VLOOKUP tutorial will show how to add different arguments in VLOOKUP.
Adding the first arguments
Now, first argument will be lookup value. Lookup value indicate which items you are searching for or which cell value you are searching. In this, you can specify directly value or cell name. In this VLOOKUP example, our task is find student marks from science subject.
We are looking for student 7 marks from science subject. We can supply “Student 7” as argument 1 or E4 as argument one. If you are matching multiple record using VLOOKUP formula then always give cell name as first argument.
Adding the second arguments
The second argument is table array indicate, where to look up? It is the cell range or data table that contains the data. In this VLOOKUP tutorial, our data is in A2:C11. Add comma to separate each argument.
Now our Formula is =VLOOKUP (“Student 7”, A2:C11 or =VLOOKUP (“E4”, A2:C11
Adding the Third arguments
The third VLOOKUP argument is the column number. In this example, we want to find science marks so use 2 as column number. If you looking for maths marks then use 3 as column index number.
Now our Lookup Formula is =VLOOKUP (“Student 7”, A2:C11, 2 or =VLOOKUP (“E4”, A2:C11, 2
Adding the Last arguments in VLOOKUP formula
The fourth argument tells us, whether to look exact match or approximate matches. You can supply 1 (True) or 0 (False), 1 for approximate matches and 0 for exact matches. Mostly, I preferred exact match i.e. 0. If first column have numerical values then you can use 1.
So, after giving all arguments our VLOOKUP formula is:
=VLOOKUP (“Student 7”, A2:C11, 2, 0) or =VLOOKUP (“E4”, A2:C11, 2, 0)
That’s it! just click enter button, it should give you answer.
Note: While dragging formula use $ before table array. E.g. =VLOOKUP (“E4”, $A$2:$C$11, 2, 0)
#VALUE! – If column index number is less than 1 then VLOOKUP will shows #VALUE VLOOKUP error.
#REF! – If column index number is greater than column index number then VLOOKUP formula will shows you #REF! VLOOKUP error.
#N/A – If Lookup value is smaller than previous value then resultant value will be #N/A error.
Due to some error if your excel is corrupted then dont worry! Try, how to recover data from corrupted excel.
VLOOKUP tutorial -Excel Vlookup Examples
I hope the VLOOKUP function is familiar to you. Let’s explore a few real world examples using VLOOKUP formulas. In this excel VLOOKUP tutorial, I already explain simple VLOOKUP example, now go to more conflicted test.
VLOOKUP in Excel 2013 from another worksheet
In real world, VLOOKUP is mostly used to find data from another worksheet. In other word, VLOOKUP is used as SQL JOIN to join data from two different table. Formula will match and pull look up value from different sheet.
Check our VLOOKUP Youtube Video Tutorial
Formula for lookup value from sheet 2 =VLOOKUP (A2, Sheet2! A2:B15, 2, 0).
Here, I am looking for A2 value from sheet 2, where A2:B15 is my array table from sheet 1 and I replacing 2nd value if exact match is found. So, if you looking VLOOKUP formula to get value from another sheet then just use (Sheet Name) and “!” before your array table (Argument 2).
In next VLOOKUP Video Tutorial, I explain how to use VLOOKUP between two different sheet without typing sheet name.
How to do VLOOKUP from a different excel sheet
Like VLOOKUP from another worksheet you can use VLOOKUP from different excel sheet. Following formula is used to fetch value from excel2 into excel1.
Here, I am looking A2 value from excel1, which is exactly match with A2:B15 table in excel 2 Keep in mind that, type formula in excel sheet 1.
Note: No space between any characters.
VLOOKUP example based on a single criteria
Let’s check VLOOKUP YouTube Tutorial on different excel sheet using single criteria.
In this VLOOKUP example, our task is to find out revenue and net income/ loss based on group name. So, group name is lookup value in this excel VLOOKUP example. It is VLOOKUP example between two sheets with single matching value.
VLOOKUP example based on a multiple criteria
In this VLOOKUP example,you will learn how to use VLOOKUP formula for multiple lookup value. Here, Revenue is calculated based on “Group Name” and “Group Segment”. Sometimes, single lookup value is not enough to find result. So, here is one simple trick and it is based on CONCATENATE function. CONCATENATE is one of the excel string formula that use to Concatenate two different strings together. If resultant value is based on multiple input then CONCATENATE is used to join two different strings together.
In this example, CONCATENATE(B2,”-“,C2) is used to concatenate “Group Name”, “-” and “Group Segment”. Once you join different string then use VLOOKUP function to calculate revenue based on new derived input as lookup value (CONCATENATE value).
Basic things to remember to get best result from Excel VLOOKUP tutorial!
- You can’t replace value as first column, Excel VLOOKUP formula unable to look at its left side. You third argument always greater than or equal to 2. That means column index is always 2 or more.
- If third argument is 1 then it return #VALUE! Error.
- In case column index is greater than table array then it produce #REF! Error.
- In my excel VLOOKUP video tutorial already shows that it is case-insensitive formula, means that uppercase and lowercase both are same.
- If the lookup value is smaller than previous lookup value then VLOOKUP function returns the #N/A error.
- To get exact match result use True or 0 as forth argument.
- If you searching for approximate match, then use False or 1 as forth argument.
- To avoid #NA error, while dragging excel VLOOKUP formula use $ in table array.
In this Excel VLOOKUP tutorial, I try to cover all the functionality of lookup function. It’s include VLOOKUP video as well as text tutorial. You will get different VLOOKUP in excel example including VLOOKUP example with multiple criteria, VLOOKUP on different sheet and VLOOKUP on two workbook.