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...
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.
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.
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.
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.
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.
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 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.
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.
It is possible to perform a VLOOKUP across different Excel files. Here you will learn three different methods which will all achieve this.
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.
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.
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.
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.
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.
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.
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.
Here you will learn how to return a complete record using a VLOOKUP.
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.
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.
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.
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.
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.
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.
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.
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.