Buying for a Team? Gift This Course
Wishlisted Wishlist

Please confirm that you want to add VLOOKUP, MATCH, INDEX, INDIRECT, TRIM Excel Functions & More to your Wishlist.

Add to Wishlist

VLOOKUP, MATCH, INDEX, INDIRECT, TRIM Excel Functions & More

Learn VLOOKUP, MATCH, INDEX, IFERROR, LEFT, RIGHT, MID, FIND, SEARCH, LEN, SUBSTITUTE, REPLACE, TRIM, CLEAN & INDIRECT.
4.7 (11 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.
44 students enrolled
Last updated 3/2016
English
$10 $20 50% off
2 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 2.5 hours on-demand video
  • 49 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Have a coupon?
What Will I Learn?
Efficiently combine data across tables
Quickly compare data across tables
Perform lookups across different sheets and workbooks
Lookup data based on multiple criteria
Lookup a complete record
Lookup duplicates
Effectively prepare data for lookup using Text Functions
View Curriculum
Requirements
  • Students will need a copy of Excel version 2007 or later
Description

This course will teach you how to use lookup functions in Excel. Lookup functions are used frequently to compare and combine data. In our experience as workplace IT trainers, lookups are the most frequently requested training topic.  Each lecture includes 2 sample Excel files: one for you to complete and one that has the solution already supplied.

What Do Lookups Do?

Lookups allow you to search for a value in one column and return the corresponding value in another column – for example search for a name to return a telephone number.

Lookups allow you to combine data from several tables so that all the information is one place.

Lookups allow you to compare date – for example sales of a product across three different stores or across three years.

Why Learn About Lookups?

I have 20 years experience of teaching Excel in the workplace across hundreds of different companies and lookup is the number one requested topic.

You need to be competent in lookup formulas to have any credibility as an Excel professional.

Companies are increasingly using Excel to manage huge amounts of data and lookup formulas are one of the skills you definitely need if you want to be able to contribute.

This Course Covers the Following Excel Functions

VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, IFERROR, ISNA, IFNA, LEFT, RIGHT, MID, FIND, SEARCH, LEN, SUBSTITUTE, REPLACE, TRIM, CLEAN & INDIRECT.

It Also Covers...

  • The Intersection operator
  • Concatenation
  • Creating drop down lists
  • PivotTable creation
  • Naming ranges
  • Converting ranges to Excel tables




Who is the target audience?
  • This course is suitable for students who need to fast track their lookup skills. No previous knowledge of lookup functions is required but a grasp of basic Excel concepts is.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 25 Lectures Collapse All 25 Lectures 02:27:26
+
Why Learn Lookup Functions?
1 Lecture 05:07

An introduction to the content and purpose of this course.

Preview 05:07
+
Getting to Grips with the VLOOKUP, HLOOKUP & LOOKUP Functions
12 Lectures 58:09

Here you will learn how to perform an exact match VLOOKUP. An exact match allows you to combine or compare data across tables where Excel can find a precise match with the data you are looking up. This example combines data from several sales reports into one table.

Preview 09:53

This is the a second example of an exact match VLOOKUP. Here you will use a VLOOKUP formula to lookup the correct discount for different product categories.

Exact Match VLOOKUP Example 2
03:09

In this third example you will use a VLOOKUP formula to calculate a deadline date and time for each call logged on a problem reporting system. You will have to use an exact match VLOOKUP to achieve this.

Exact Match VLOOKUP Example 3
03:00

In this first example of an approximate match VLOOKUP you will learn that VLOOKUP can look up a value in a range of values. We will look up a sales value in a bonus table to return the correct bonus. The sales value won't need to find an exact match to return the correct bonus.

Approximate Match VLOOKUP Example 1
03:11

In this example you will learn how to grade students based on the exam mark. An approximate match VLOOKUP is appropriate in this situation as you will need to lookup the mark within a range of marks that are awarded a particular grade.

Approximate Match VLOOKUP Example 2
01:36

Use an HLOOKUP to look up values in a table arranged in rows rather than columns. The HLOOKUP function is very similar to VLOOKUP but it looks up horizontally rather than vertically.

The HLOOKUP Function
01:46

The LOOKUP function allows you to perform lookups on both vertical and horizontal tables.  With a LOOKUP function you don't need to worry about the order of the columns or rows in your table (as you do with VLOOKUP and HLOOKUP). The LOOKUP function only performs an approximate match.

The LOOKUP Function
03:20

If you need to perform a VLOOKUP across worksheets this tutorial describes three methods that will achieve this. As well as learning how to perform a VLOOKUP across sheets you will learn how to name a reference and how to create an Excel table with a dynamic range.

Perform a VLOOKUP Across Different Worksheets - 3 Different Methods
08:50

It is possible to perform a VLOOKUP across different Excel files. Here you will learn three different methods which will all achieve this.

Performing VLOOKUP Across Workbooks - 3 Different Methods
06:06

We need to produce a PivotTable report but the information we have is on three different sheets.  To combine all the data into one table we can use VLOOKUP.

VLOOKUP and PivotTable
06:22

The lookup functions return an #N/A error if the value you are looking for cannot be found. The IFERROR and IFNA functions allow you to convert the error into a more useful value. Please note IFNA is only available in Excel 2013 and later. For an alternative to IFNA please see the next video on ISNA.

Error Handling with IFERROR and IFNA
05:59

The ISNA function combined with an IF function is an effective way of handling the #N/A error. This solution is useful if you have a version of Excel earlier than Excel 2013.

Error Handling with ISNA
04:57
+
Performing a Two Way Lookup (Vertical and Horizontal Lookup) - 3 methods
6 Lectures 40:58

Here you will learn how the MATCH function can return the position of a value within a row or a column. This will prove useful later on in the course when you want to perform a two way lookup.

Preview 04:37

By combining VLOOKUP with MATCH you will be able to perform a two way lookup: that is a lookup that locates a value in a table by specifying a vertical and horizontal position.

VLOOKUP & MATCH
06:12

When you combine the MATCH function with the INDEX function you can look up a value in a table based on its vertical and horizontal position.  You can also use the INDEX function to lookup your values across multiple tables.

INDEX & MATCH
10:54

The intersection operator provides another way of performing a 2 way lookup.  You will learn about this operator and how to use with the INDIRECT function. 

Preview 04:18

Here you will learn how to return a complete record using a VLOOKUP.

Lookup and Return a Complete Record
07:30

The way VLOOKUP works you are forced to lookup a value in the left most column of your lookup table and then return a value to the right. If your data is arranged the other way around you will need to know how to look up to the left. This tutorial will show you a couple ways of achieving this.

Lookup to the Left
07:27
+
Get the Data to Match with Text Functions
6 Lectures 43:12

The LEFT, MID and RIGHT functions are useful in a lookup scenario as they allow you to extract the useful part of a text string - the portion that matches values in another table.  For example if your product code reads 1234-ABC in one table and 1234-A in another you can make them match by using these functions.

Preview 04:47

For trickier manipulation of text values you will need to use SEARCH, FIND and LEN in conjunction with some of the other text functions. This part of the course explains how they work and why they are so useful.

Find the Position of Characters Using SEARCH, FIND & LEN
13:31

Superfluous spaces in text values can be a real problem when you want to perform a lookup. If someone has accidentally added spaces they are really hard to spot especially when they exist at the end of a text string as they are invisible. In this part of the course you will learn how to use the TRIM function to solve this issue.

Use TRIM to Get Rid of Unwanted Spaces
04:36

The CLEAN function deletes non-printing characters that you may have imported with your data from other systems. Like spaces these non-printing characters often prevent a lookup from being achieved. Here you will learn how the CLEAN function works and which non-printing characters it is able to remove from a text string.

Use the CLEAN Function to Get Rid of Unwanted Non-printing Characters
04:16

The SUBSTITUTE and REPLACE functions are useful in the context of lookup as they allow you to manipulate text values so they match between tables. A common scenario would be where a product code is displayed with a prefix or hyphens when imported from one system but not from another.

SUBSTITUTE & REPLACE Characters
10:14

Lookup functions don't cope very well with duplicates - they ignore all but the first occurrence.  Here you will learn a couple of ways to deal with duplicates.

Join (Concatenate) Data to Enable Duplicate Lookup
05:48
About the Instructor
4.7 Average rating
11 Reviews
44 Students
2 Courses
Excel Trainer at Mousebytes

Director of UK based IT training company. IT trainer with 20 years IT training experience in a commercial and public sector context. I have run MS Office courses in literally hundreds of companies, providing tailored training made highly relevant to the workplace. These Udemy courses are the result of these many years' of experience.

Report Abuse