EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel VLOOKUP in Excel
Secondary Sidebar
Excel Functions
  • Lookup and Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Compare Two Columns in Excel using VLOOKUP
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (220+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

VLOOKUP in Excel

By Madhuri ThakurMadhuri Thakur

VLOOKUP in Excel

VLOOKUP in Excel (Table of contents)

  • VLOOKUP Function in Excel
  • Steps for Using VLOOKUP Function
  • How to Use VLOOKUP in Excel?

Introduction to VLOOKUP Function in Excel

VLOOKUP in Excel is used to lookup the value with a reference cell and fetch the value from the selected lookup table array and is quite useful and one of the most widely used excel functions We can use a table or single column to lookup the value. And all the lookup can be done in a vertical zone or with columns only.

Start Your Free Excel Course

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

VLOOKUP Formula in Excel:

VLOOKUP Formula

The vlookup function uses of the arguments:-

There are four arguments in vlookup function which is below mention:

  1. Lookup value (required argument) – it is the value that we want to look up for in the column of a table. Where you want or get the value from another table.
  2. Table array (required argument) – it is the data array that is to be searched. The vlookup function searches in the left-most column of this array. We can say that this is a matching table.
  3. Column index number (required argument) – an integer, specifying the column number of the supplied table array, that you want to return a value from. If you only using it for data matching then you can put 1 but if you want to get a value from another column behave on matching the lookup value then you need to put the column no from matching column no.
  4. Range lookup (optional argument) – it defines what this function should return in the event that it does not find an exact match to the lookup value. The argument can be set to true or false, which means:
  • True – approximate match, that is, if an exact match is not found, use the closest match below the lookup value.
  • False – exact match, that is, if an exact match not found then it will return an error.
  • We can also use 0 for false and 1 for true matching.

Steps for Using VLOOKUP Function

  • Click on formula tab > lookup & reference > click on vlookup.
  • Also, click on the function icon then manually write and search the formula.
  • We get a new function window showing in the below mention pictures.
  • Then we have to enter the details as shown in the picture.
  • Put the lookup value where you want to match from one table to another table value.
  • You need to put the table array which is another table value.
  • Put the col index number for another table vertical value which is a need.
  • Rage lookup false for exact match and true for an approximate match.
  • You can also use 0 for an exact match and 1 for an approximate match.

VLOOKUP(formula tab)

Shortcut for using the Formula

Click on a cell where you want to result in value then put the formula as below mention

= vlookup (lookup value, table range, column index) > enter

Explanation for VLOOKUP Function:

This function helps you to locate specific information in your spreadsheet. When the user uses the vlookup function for finding specific information in an MS Excel spreadsheet, each matching information is displayed in the same row but in the next column. This function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index number position.

We can also use one sheet to another sheet and one workbook to another workbook also

How to Use VLOOKUP in Excel?

Vlookup function is very simple and easy to use. Let us understand the working of vlookup. Below mention are the details using the formulas.

You can download this VLOOKUP Function Template here – VLOOKUP Function Template

Example #1 – Exact Match

To search for an exact match, you put false in the last argument.

VLOOKUP (last argument)

As above mention in table b there is all information of the employee like department, employee id, address mobile no, etc. You can suppose as array table or master table data. And you have another table where required only contact no of the employee. So as the employee name is a unique column then the employee name a lookup value in a table where you want to get a result.

Master data table b as above showing is a table array, and in master data, you can see that mobile no column is on the 5 number column index. Then we need to put the 0 for exact matching or 1 for false matching.

Vlookup Function

You can see the result here:

Formula: – “=vlookup(a21,a1:e12,5,0)”

VLOOKUP Result

We can see that in a table all values are matching the exact value.

table all value

Example #2 – Approximate Match

an approximate match

As above showing in the picture in the H column, there is employee age mention and column I employee name. You can take this as an array or master table. Now I want to put the age value in k2 then we get the employee name which is approximate age as given in k2 age value as showing below mention pictures.

approximate age

So lets we check the formula now.

“=vlookup(k2,h2:i12,2,true)”

VLOOKUP name

Then we can get the approximate matching value.

approximate matching value.

As you see, the formula returns Mr. Puneet Sharma whose age is 43, while we also have Mr. Manish patial that age 48 but 43 is much closer to 44 than 48. So, why does it return Mr. Puneet? Because vlookup with approximate match retrieves the closest value that is less than the lookup value.

Things to Remember

  • The vlookup function returns result in any data type such as a string, numeric, date, etc.
  • If you specify false for the approximate match parameter and no exact match is found, then the vlookup function will return #n/a.
  • If you specify true for the approximate match parameter and no exact match is found, then the next smaller value is returned.
  • If the index number is less than 1, the vlookup function will return the #value!.
    If the index number is greater than the number of columns in the table, the vlookup function will return #ref!

Recommended Articles

This has been a guide to VLOOKUP Function. Here we discuss the VLOOKUP Formula and how to use the VLOOKUP function along with an excel example and downloadable excel templates. You may also look at these useful functions in excel-

  1. VLOOKUP Examples in Excel
  2. VLOOKUP Tutorial in Excel
  3. VBA LOOKUP
  4. IF VLOOKUP Formula in Excel
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Primary Sidebar
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

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

Let’s Get Started

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

EDUCBA

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

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

Download VLOOKUP Function Template

EDUCBA Login

Forgot Password?

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

EDUCBA

Download VLOOKUP Function Template

EDUCBA

डाउनलोड VLOOKUP Function Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more