EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips VLOOKUP Tutorial in Excel

VLOOKUP Tutorial in Excel

Arun Gupta
Article byArun Gupta
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 21, 2023

VLOOKUP Tutorial in Excel

VLOOKUP Tutorial in Excel

VLOOKUP is an in-built function in MS Excel. A user can look up and get the data from a column of a table or sheet with the VLOOKUP Excel function’s help. In this article, we will learn about VLOOKUP Tutorial in Excel.

Start Your Free Excel Course

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

VLOOKUP function is very popular in MS Excel. A user can look for an exact match or a part of the value (approx. match). Here ‘V’ stands for Vertical in VLOOKUP, meaning it will only work on the column.

Syntax of VLOOKUP Formula

Below is the syntax of the VLOOKUP formula:

VLOOKUP Formula in Excel

Arguments of VLOOKUP Function:

  • lookup_value: A mandatory field provides the value the user wants to search in the column or range. If the value is not found in the range or table array, it will return the ‘# N/A’ error.
  • table_array: A mandatory field is a range where users want to look up the value. It should contain all the values of the Col_index_num.
  • col_index_num: It is a mandatory field, the column number in the table_array from the first column of table_array. It will return the ‘#REF!’ error if the number of column_index_num is greater than the number of columns in table_array
  • range_lookup: It is an optional field; by default, it will take as an exact match. It has two options, either true or false.
  • FALSE – It will check for the exact match in the range. For this, provide value as 1. In this, the column must be sorted numerically or alphabetically.
  • TRUE – It will check for the approximate match in the range. For this, provide a value of 0.

How to Use VLOOKUP Tutorial in Excel?

Excel VLOOKUP Tutorial is very simple and easy. Let’s understand How to Use VLOOKUP Tutorial in Exel with some examples.

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

Excel VLOOKUP Tutorial – Example #1

How to look up the Approximate Match Value in the Table.

A company called ‘ABC Consultancy Services’ has employee details like First Name, Last Name, City, and Skills.

Le’s consider if a user wants to find the approximate match as the Skill ‘Python’ in the company ‘ABC Consultancy Services’ employee details. So how to get the required Skill Employee First name.

Open MS Excel, and go to Example #1 sheet where the company keeps employee details.

VLOOKUP Tutorial in Excel Example 1-1

The user wants to find the Required Skill, Employee, and First name. So apply the VLOOKUP formula.

  • As mentioned in Python’s above-required skill, the lookup value is Python, which is in cell G3. lookup_value = G3

VLOOKUP Tutorial in Excel Example 1-2

  • Now provide the input for the table array, which is A3 to D15. table_array: A3: D15

VLOOKUP Tutorial in Excel Example 1-3

  • Now provide the column index input, which is 1 because the user wants to find the first name of the required skill. col_index_num: 1

VLOOKUP Tutorial in Excel Example 1-4

  • Now provide the input for the range lookup, which is TRUE because the user wants to find the approximate match of the lookup value. range_lookup: TRUE

VLOOKUP Tutorial in Excel Example 1-5

  • Now press the Enter button so the VLOOKUP formula in the table is applied, which is =VLOOKUP (G3, A3:D15, 1, TRUE)

Result of Example 1-6

Summary of Example #1:

The user wanted to find the required skill employee’s first name from the employee details table, and the result is Jordan because of the VLOOKUP formula.

If he wants to find any other skill, he just needs to change the VLOOKUP value, which is G3, in the Example #1 sheet, and he will get the right answer for his problem.

Excel VLOOKUP Tutorial – Example #2

How to look up the Exact Match Value in the Table.

A company called ‘ABC Consultancy Services’ has employee details like First Name, Last Name, City, and Skills.

Let us consider if a user wants to find the exact match as the required Last Name of Jenifer in the employee details of the company ‘ABC Consultancy Services.’ So how to get the required Last Name of Jenifer with the exact match.

Open MS Excel, and go to Example #2 sheet where the company keeps employee details.

VLOOKUP Tutorial in Excel Example 2-1

As the user wants to find the last name of Employee Jenifer, so apply the VLOOKUP formula.

  • As mentioned in the required Last name of Jenifer, the lookup value is Jenifer, who is in G3. lookup_value = G5

VLOOKUP Tutorial in Excel Example 2-2

  • Now provide the input for the table array, which is A3 to D15. table_array: A3: D15

VLOOKUP Tutorial in Excel Example 2-3

  • Now provide the column index input, which is 2 because the user wants to find the Last Name of the required skill. Which is available in the 2nd column of the table array. col_index_num: 2

Example 2-4

  • Now provide the input for the range lookup, which is FALSE because the user wants to find the exact match of the lookup value. range_lookup: FALSE

Example 2-5

  • Now press the Enter button so the VLOOKUP formula in the table is applied, which is =VLOOKUP (G3, A3:D15, 2, FALSE)

Result of Example 2-6

Summary of Example #2:

As the user wanted to find the required Last Name of the employee’s first name, Jenifer, Lenger can be seen from the employee details table because of the VLOOKUP formula.

If he wants to find any other Employee’s last name, he just needs to change the VLOOKUP value, G3 in Example #2, and get the right answer for his problem.

Things to Remember

  • If the col_index_num is greater than the number of columns in table_array, it will return the ‘#REF!’ error.
  • If the VLOOKUP value is not found in the range or table array, it will return the ‘# N/A’ error.
  • If a user is not providing the value for Range_lookup, then by default, it will take as TRUE, which will be an approximate match.
  • In the Range_lookup, the table data should be sorted alphabetically or numerically. It will return the closest match.
  • If a user wants to give cell no or that value itself, he can give in the VLOOKUP value argument.

Recommended Articles

This has been a guide to VLOOKUP Tutorial in Excel. We discussed using the VLOOKUP tutorial in Excel, practical examples, and a downloadable Excel template. You may also look at the following articles to learn more-

  1. Excel Alternatives to VLOOKUP
  2. VLOOKUP with Sum in Excel
  3. VBA VLOOKUP Function
  4. VLOOKUP Examples in Excel
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • 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

Download VLOOKUP Tutorial Excel Template

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
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
EDUCBA

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

Forgot Password?

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

EDUCBA

Download VLOOKUP Tutorial Excel Template

EDUCBA

डाउनलोड VLOOKUP Tutorial Excel 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