How to use Vlookup in Excel - A simplest tutorial
4.1 (267 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
4,480 students enrolled
Wishlisted Wishlist

Please confirm that you want to add How to use Vlookup in Excel - A simplest tutorial to your Wishlist.

Add to Wishlist

How to use Vlookup in Excel - A simplest tutorial

Learn Lookup functionality in just 50 minutes
4.1 (267 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
4,480 students enrolled
Last updated 12/2013
English
Price: Free
Includes:
  • 1 hour on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
How to use references, Vlookup, Hlookup, Lookup, Index & Match
Learn whole lookup functionality from Microsoft Certified trainer from your own desk.
Suitable for beginners to advanced users. ideal for users who learn faster when shown.
Functions explained in most logical and simplest manner
Downloadable exercise files for practical learning
View Curriculum
Requirements
  • Microsoft Excel 2007/2010/2013
Description

In this lookup functionality video course, Nurture Tech Academy introduces you to the most simplest way to understand Vlookup and other lookup functions.

In this Excel training course, you will learn by watching the author actually perform the operation he is instructing on, as he explains step by step how to perform these functions. The training starts with understanding references and how excel reacts on the same. Then it will show you how to use Vlookup and then gradually moves to the next level in lookup functionality like Vlookup with False, Vlookup with True, Vlookup with if function, Hlookup, Lookup and ultimately combines Index & Match.

By the completion of this online training course, you will be fully versed, and capable of using any function in lookup functionality in Microsoft Excel 2007/2010/2013 in a commercial Environment.

Who is the target audience?
  • Working Professionals & Students
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 8 Lectures Collapse All 8 Lectures 51:40
+
First thing first - References in Excel
1 Lecture 07:22

In this lecture we will cover 4 types of references. It is really important to know how excel reacts on references. The references available in excel are as follows:-

1. Relative Reference - In this case if you refer a cell address it will not follow that address strictly i.e. it will change if you copy and paste the same on another cell.

2. Absolute Reference - In this case it will follow the same cell address everywhere when we copy and paste the same.

3. Mixed Reference - Under Mixed reference, we can either freeze a column of a row whenever we refer a cell.

4. 3D Reference - Whenever we refer a cell on a different sheet or a different workbook it is known as a 3D reference.

4 types of references
07:22
+
Understanding Vlookup in-depth
4 Lectures 26:37

You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. In this lecture we will learn how to use Vlookup with False or Exact Match. Whenever we have values exactly available in our lookup range we can use Vlookup with False. This will help us to get the final result.

Vlookup with False
10:40

In the earlier lecture we have learnt how to use Vlookup with False when we have exact lookup values available but what if, we have some approximate values available for lookup. In that case we can use Vlookup with True. We can either choose True from the dropdown list or we can write 1 in that argument. Both will work fine.

Vlookup with True
05:20

In this lecture we will cover how to use vlookup with Mixed Reference. Let me tell you guys this will help a lot in saving time while working because a number of times we face a situation in which we have to lookup the values from a data range but we have to keep either column or row to be freeze for further references. If you are not clear about how to use Mixed Reference then i'll advice you to please go through with the first lecture again and fully verse with references part and then jump into this lecture.

Vlookup with Mixed References
05:19

With this lecture i assume that you know how to use a logical function like "IF". Let me just give you a brief about IF function that, as per its syntax i.e. (logical_test, [value if true], [value of false]) it judges the condition we give it and on the basis of that it apply the result e.g. if i toss a coin, it will be considered as a "logical_test" , now if i demand heads so this is my condition so if this condition will become true than excel ask us that what we want it to show. It means that i demanded heads so i say that if head comes then this formula will show "You Win" otherwise "You loose" so i'll assign "You Win" to [Value if true] and "You loose" to [Value of false].

So we will club this function with Vlookup and tell excel to lookup values on the basis of some conditions.

Vlookup with IF functionality - a nested function
05:18
+
Understanding Hlookup
1 Lecture 04:03

The Microsoft Excel HLOOKUP function searches for value in the top row of table_array and returns the value in the same column based on the index_number

The syntax for the Microsoft Excel HLOOKUP function is:

HLOOKUP( value, table_array, index_number, [not_exact_match] )

value is the value to search for in the first row of the table_array.

table_array is two or more rows of data that is sorted in ascending order.

index_number is the row number in table_array from which the matching value must be returned. The first row is 1.

not_exact_match is optional. It determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the HLOOKUP function will look for the next largest value that is less than value. If this parameter is omitted, HLOOKUP will return an approximate match.

Hlookup - lookup in horizontal way
04:03
+
Lookup - No "V" No "H" only lookup
1 Lecture 04:11

In this lecture we will see how to use lookup.

The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the LOOKUP function:

In Syntax #1, the LOOKUP function searches for value in the lookup_range and returns the value in the result_range that is in the same position.

The syntax for the Microsoft Excel LOOKUP function is:

LOOKUP( value, lookup_range, [result_range] )

value is the value to search for in the lookup_range.

lookup_range is a single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range.

result_range is optional. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, the LOOKUP function will return the first column of data.

Only Lookup
04:11
+
Last option in Lookup Functionality - Index & Match
1 Lecture 09:27

When deciding between which vertical formula to use in excel, the majority of Excel experts agree that Index & Match is a better formula than Vlookup. However, many people still resort to using VLOOKUP because it’s a simpler formula. One major driver of this problem is that most people still don’t fully understand the benefits of switching from VLOOKUP, and without such an understanding, they are unwilling to invest the time to learn the more complex formula.

In this lecture i'll explain you the advantage of using Index and Match togather.

Index and Match - The power of two
09:27
About the Instructor
Nurture Tech Academy
4.0 Average rating
350 Reviews
11,729 Students
5 Courses
Microsoft Office Specialist

Nurture Tech Academy (http://nurturetechacademy.in/) is Microsoft Authorised Testing Centre, providing blend of instructor-led and online training programs in Basic Excel, Advanced Excel, VBAs, Macros, and other MS Office applications. It has a team of Microsoft Certified Trainers who are specialist in their respective fields.