EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VLOOKUP Examples in Excel

By Madhuri ThakurMadhuri Thakur

Home » Excel » Blog » Excel Tips » VLOOKUP Examples in Excel

VLOOKUP examples in excel

Introduction to VLOOKUP Examples in Excel

This article covers one of the most helpful features, which is VLOOKUP. Simultaneously it is one of the most complex and less understood functions. In this article, we will demystify VLOOKUP with a few examples.

Examples of VLOOKUP in Excel

VLOOKUP in Excel is very simple and easy to use. Let’s understand how to use the VLOOKUP in Excel with some examples.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

You can download this VLOOKUP Examples Excel Template here – VLOOKUP Examples Excel Template

Example #1 – Exact Match (False or 0)

Here for this example, let’s make a table to use this formula; suppose we have the data of students as shown in the image below.

vlookup examples in excel 1-1

In cell F2, we are using VLOOKUP Formula. We have made the table; by using Vlookup, we will find the Last name of the person from their first name; all the data has to be available in the table which we are providing the range to find the answer within (A:C in this case).

vlookup examples in excel 1-2 

  • We can select the table too instead of the entire row; In the formula, you can see written 2 before False as a column indicator because we want data to be retrieved from column # 2 from the selected range.
  • Here when we place data in E3, which is the first name from our given table, the formula will give us the Last Name.
  • When we enter the first name from a table, we are supposed to get the formula’s last name.
  • From the Image above, we can see that by writing the name Rahul in the column, we got Dravid the second name according to the table on the left.
  • Now we can also widen the data from a single data available, so by just filling the First Name, we will have the Last name of the person and the runs scored by that person.

vlookup example 1-3

  • So let’s add the third column on the right side, “Runs”.

vlookup example runs

  • After applying VLOOKUP Formula, the result is shown below.

vlookup example runs 2

  • We can see that just from the first name, we have got the last name and the runs scored by that student; If we retrieve runs from the last name column, it will be called chained Vlookup.
  • Here you can see we have written 3 as we want the data of column #3 from the range we have selected.
  • Here we have used False OR 0, which means it is matching the absolute value, so by applying even extra space to our first name, it will show the #N/A means data not matching.

Example #2 – Approximate Match (True or 1)

  • If we apply the same formula with True or 1 instead of False or 0, we will not have to worry about providing the exact data to the system.
  • This formula will provide the same results, but it will start to look from top to bottom and provides the value with the most approx match.
  • So even after you make some spelling mistake or a grammatical error in our case, you don’t need to worry, as it will find the most matched data and provides the result of mostly matched.

Approximate Match 1

  • After applying VLOOKUP Formula, the results are shown below.

Approximate Match 2

  • In our example, we have written Mahendra in the first name, and still, we are getting the result correctly; this can be a bit complicated while you’re playing with the words but more comfortable while you’re working with Numbers.
  • Suppose we have Numeric data instead of the name or word or alphabetical; we can do this more precisely and with more authentic logic.
  • It seems pretty useless as here we are dealing with small data, but it is very useful while dealing with large data.

Example #3 – Vlookup from a Different Sheet

  • Vlookup from a different sheet is very much similar to the Vlookup from the same sheet, so here we have changed in the ranges, as here we have different worksheets.
  • We have a table in sheet number 2 as per the following image; we will find the result of this student in sheet number 3 from their roll nos.
  • As you can see, the image on the right side is of another sheet, sheet number 3.

Vlookup from different Sheet 2

  • Apply the VLOOKUP Formula in Column B.

Vlookup from different Sheet 1

  • After applying VLOOKUP Formula, the result is shown below.

vlookup example 1-4

  • Drag down the formula for the next cell. So the output will be as below.

vlookup example 1

  • From the above images, you can see that the range of the formula has been indicated with ‘Example#2′ as the data we needed will be retrieved from Example #2 and column #3. So as a column indicator, we have written 3, and then 0 means False as we want exact data match.
  • As you can see from the below image, we have successfully retired the entire data from Example #2 to Sheet # 3.
  • You can see from the above image, after getting the value in the first row, we can drag the same and get to throughout data against the given roll nos if it is available in the given table.

Example #4 – Class Using Approx Match

  • To define class from the marks, we can use an approx match to define the class against the Marks.

Class Using Approx Match 1

After using the VLOOKUP formula, the result is shown below

vlookup examples 1

  • As per the above image, you can see that we have made a table of students with marks to identify their class; we have made another table that will act as the key,
  • But make sure that the marks in the cell have to be in ascending order.
  • So in the given data, the formula converts the marks into a grade from the class table or “Key” table.
  • Drag the same formula from cell C2 to C9.

vlookup example 2

Example #5 – Dual Lookup Using Match function

  • The Match function is used when we need to lookup two-way data; here, from the above table, you can see there is data of batsman against the runs scored by them in particular years.
  • So the formula to use this match function is as follows:

=vlookup(lookup_Val, table, MATCH(col_name, col_headers,0),0)

Dual lookup using Match function 1

  • After applying the Formula, the result is shown below.

Dual lookup using Match function 2

  • Here if we study the formula, we can see that our data is dependant on two data; in our case, what we write in G1 and G2, in the Match function, we have to select the Column Headers, any of the headers have to be written in G2 which is dedicated to the column name.
  • In column G1, we have to write the data from the Row of the Players Name.
  • The formula becomes as follows:

=vlookup(G1, A:D, MATCH(G2, A1:D1,0),0)

  • Here in the given formula, MATCH(G2, A1:D1,0) is selected column headers cell G2 has to be filled by one of the headers from the selected column header.
  • It returns that Dhoni has scored 3800 Runs in 2018.

Recommended Articles

This is a guide to VLOOKUP Examples in Excel. Here we discuss using VLOOKUP in Excel and some practical examples, and a downloadable excel template. You can also go through our other suggested articles –

  1. IF VLOOKUP Formula in Excel
  2. VLOOKUP Function in Excel
  3. VLOOKUP Tutorial in Excel
  4. VBA VLOOKUP Function

MS Excel Training Bundle

13 Online Courses

100+ Hours

Verifiable Certificates

Lifetime Validity

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Excel Tips
    • Recover Document in Excel
    • Excel Shortcut to Select Row
    • Excel Header Row
    • Excel Insert Button
    • Excel Translate
    • Budget in Excel
    • KPI Dashboard in Excel
    • Blank Invoice Excel Template
    • Write Formula in Excel
    • Auditing Tools in Excel
    • Autofit Row Height in Excel
    • Formatting Text in Excel
    • Add Rows in Excel Shortcut
    • Divide Cell in Excel
    • Compare Two Columns in Excel for Matches
    • Excel Troubleshooting
    • Unprotect Excel Workbook
    • Subtract Date in Excel
    • Carriage Return in Excel
    • Negative Numbers in Excel
    • Worksheets in Excel
    • Excel Match Multiple Criteria
    • Exponential Moving Average Formula
    • Sentence Case in Excel
    • Excel Reverse Order
    • Excel Timesheet Template
    • Weighted Average in Excel
    • CTRL Shift-Enter in Excel
    • Excel Expense Tracker
    • INDEX MATCH Function in Excel
    • Percent Change in Excel
    • Drag and Drop in Excel
    • Shade Alternate Rows in Excel
    • Excel Database Template
    • Inverse Matrix in Excel
    • New Line in Excel Cell
    • Format Cells in Excel
    • Excel Text with Formula
    • Project Management Template in Excel
    • Vlookup vs Index Match
    • Embedded in Excel
    • Count Colored Cells in Excel
    • Excel Hacks
    • Column Header in Excel
    • Database Function in Excel
    • Excel Spell Check
    • Linear Interpolation in Excel
    • Convert Excel to CSV
    • Excel Not Responding
    • Delta Symbol in Excel
    • Excel Mortgage Calculator
    • Excel Show Formula
    • Break Links in Excel
    • VLOOKUP Examples in Excel
    • Alternate Row Color Excel
    • Checklist in Excel
    • Microsoft Office Tips
    • Excel vs Access
    • Excel Change Case
    • Count Characters in Excel
    • Formula Bar in Excel
    • Invoice Template in Excel
    • Find and Replace in Excel
    • PowerPivot in Excel
    • Wildcard in Excel
    • Line Break in Excel
    • NULL in Excel
    • Text Formula in Excel
    • Family Tree in Excel
    • Insert Calendar in Excel
    • Page Setup in Excel
    • Search For Text in Excel
    • Switching Columns in Excel
    • 3D Cell Reference in Excel
    • Roadmap Template in Excel
    • Cell References in Excel
    • Excel Automation
    • Matching Columns in Excel
    • Evaluate Formula in Excel
    • How to Find Mean in Excel
    • How to Add Cells in Excel
    • Project Timeline in Excel
    • TODAY Formula in Excel
    • Poisson Distribution in Excel
    • YEAR Formula in Excel
    • REPLACE Formula in Excel
    • Create Spreadsheet in Excel
    • SEARCH Formula in Excel
    • HYPERLINK Formula in Excel
    • Pivot Table with Multiple Sheets
    • COLUMNS Formula in Excel
    • DAY Formula in Excel
    • RIGHT Formula in Excel
    • INDIRECT Formula in Excel
    • Excel IRR Formula
    • Pivot Table Sort
    • OR Formula in Excel
    • Pivot Table Formula in Excel
    • How to Edit Drop Down List in Excel?
    • WEEKDAY Formula in Excel
    • Chart Wizard in Excel
    • Time Difference in Excel
    • Lookup Table in Excel
    • SUBTOTAL Formula in Excel
    • LOOKUP Formula in Excel
    • Themes in Excel
    • Group Worksheets in Excel
    • Excel ISNUMBER Formula
    • 3D Maps in Excel
    • VLOOKUP Tutorial in Excel
    • Moving Columns in Excel
    • Grouping Columns in Excel
    • Excel Formula of Percentage
    • Excel Leading Zeros
    • Timesheet in Excel
    • Nested IF Formula in Excel
    • SUMIF Formula in Excel
    • Concatenation in Excel
    • Excel Insert Page Break
    • LEN Formula in Excel
    • Insert Comment in Excel
    • Excel Print
    • Fractions in Excel
    • Excel Add a Column
    • Borders in Excel
    • Excel Sum by Color
    • Excel vs Google Sheets
    • Calculate Percentage Increase in Excel
    • Excel Format Phone Numbers
    • Excel Search Box
    • Excel vs CSV
    • Excel vs Numbers
    • Excel Concatenate Date
    • Excel Bullet Points
    • Calculate Compound Interest in Excel
    • Find External Links in Excel
    • Excel Comma Style
    • Excel Remove Leading Spaces
    • Combine First and Last Name in Excel
    • How to Calculate Ratio in Excel
    • SUMPRODUCT Function with Multiple Criteria
    • Create Excel Template
    • Excel Greater than or Equal
    • Excel Keyboard Shortcuts
    • Excel Row vs Excel Column
    • Excel Operators
    • Excel Text Compare
    • Excel Uppercase Function
    • Excel Auto Numbering
    • Watermark in Excel
    • Excel Delete Row Shortcut
    • Excel Word Count
    • Row Count in Excel
    • Excel Grade Formula
    • Excel Percentage Difference
    • Excel Rows and Columns
    • Excel Group
    • Excel Move Columns
    • Excel Row Height
    • Excel Sparklines
    • Excel Drawing a line
    • Multiple IFS in Excel
    • Excel Strikethrough
    • Numbering in Excel
    • Share Excel Workbook
    • Excel Shortcut For Merge Cells
    • Excel OneDrive
    • Excel Track changes
    • Excel Hide Formula
    • Excel Filter Shortcuts
    • Excel 3D Reference
    • Protect Excel Workbook
    • Excel Moving Averages
    • Excel Row Limit
    • Excel Absolute Value
    • Excel Print Area
    • Excel Format Painter
    • Excel Separate text
    • Excel Autofit
    • Excel Columns to Rows
    • Excel Paste Shortcut Option
    • Excel Rows to Columns
    • Excel Insert Row Shortcut
    • Excel Extensions
    • Excel Unmerge Cells
    • Divide in Excel Formula
    • Excel Compare Two Columns
    • Not Equal To Excel
    • Excel Remove Duplicates
    • Excel Remove Spaces
    • Excel Calculate Age
    • Copy Excel Sheet
    • Excel Conditional Formatting Based on Another Cell Value
    • Excel Cell Reference
    • Excel Split Cell
    • Excel Circular Reference
    • Highlight Every Other Row in Excel
    • Errors in Excel
    • Excel Delete Pivot Table
    • Excel Pivot Table
    • Subtraction in Excel
    • Count Unique Values in Excel
    • Excel Highlight Duplicates
    • Excel Data Formatting
    • Random Numbers in Excel
    • Basic Excel Formulas
    • Page Numbers in Excel
    • Excel CAGR Formula
    • Excel Combine cells
    • IFERROR with VLOOKUP in Excel
    • Column Sort in Excel
    • Print Comments in Excel
    • Cheat Sheet of Excel Formulas
    • Remove Hyperlinks in Excel
    • Excel Drop Down List
    • Remove (Delete) Blank Rows in Excel
    • Relative Reference in Excel
    • Subscript in Excel
    • Superscript in Excel
    • Excel Insert Multiple Rows
    • Excel Absolute Reference
    • Unhide Columns in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Statistical Functions in Excel (58+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL COURSE
  • Online Excel Data Analysis Course
  • Excel for Finance Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

Download VLOOKUP Examples Excel Template

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - MS Excel Training Bundle Learn More