Excel VLOOKUP Example | VLOOKUP tutorial | VLOOKUP Function

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.

VLOOKUP Examples
VLOOKUP Examples 

 

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.

Excel Vlookup simple Examples
Excel Vlookup simple Examples

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.

Excel Vlookup Example
Excel VLOOKUP Example

VLOOKUP Syntax 

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.

=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)

 

VLOOKUP Error

#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.

VLOOKUP formula to get value from different worksheet
VLOOKUP formula to get value from different worksheet

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!

excel
excel

  1. 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.
  2. If third argument is 1 then it return #VALUE! Error.
  3. In case column index is greater than table array then it produce #REF! Error.
  4. In my excel VLOOKUP video tutorial already shows that it is case-insensitive formula, means that uppercase and lowercase both are same.
  5. If the lookup value is smaller than previous lookup value then VLOOKUP function returns the #N/A error.
  6. To get exact match result use True or 0 as forth argument.
  7. If you searching for approximate match, then use False or 1 as forth argument.
  8. 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.

 

🏅www.etrade.com Login Guide|✅US Etrade Tips And Tricks

Are you looking for some information related to Etrade login, or simply you want the guidance for Etrade login, or you just...

👉Best 2020 GUIDE For merrickbank.com login|All about Merrick Bank Login Tips and Tricks

If you want to do a successful Merrick Bank Login and if you do not understand the complete step-by-step login processes,...

👉Everything about Avid Login|✅Best Guidelines|💯For Amazing 2020

Are you looking for Avid login guidelines, or you just want the information related to Avid, or else you have some query...

👉Everything about MyVanillaDebitCard- Application, Activation & Benefits-[✅Updated 2020]

One of the major features MyVanillaDebitCard provides is that it makes cash management very simple for the...

🏅BAML Cards|✅Best Bank Of America Merrill Lynch Visa Reward Card Review 2020 {GUIDE}

Are you doubtful about the Bank Of America Merrill Lynch Card? Do you need a complete guide on the reward card? The...

Quick Tips About Intel Iris Graphics 6100 | Updated

Are you looking for the information related to Intel Iris Graphics 6100, or you just looking for Intel information to keep yourself...

Download Free Spotify Premium Apk | Spotify Mod Apk Free

Are you looking for a free spotify premium apk? If the answer the question is a yes, then you're at the right place. We've...

How To Download Xender For PC Windows 7,8,10 and Mac

Are you looking for information related to Xender application, or you looking for how to download Xender for PC, or you just...

✅Best Credit One Bank Login|🔒Secure Registration Guide|2020

Are you in search of a reliable Credit One Bank login and registration guide? If yes, then this is the place to...

🏅Everbank Login|✅Best Guidelines|For Amazing 2020

Are you looking for information about EverBank, or you just simply looking for the guidelines to log into the Everbank financial...

Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox