Course Length: 2.5 Hours
Lectures: 25 hours
Cost: £15 – offers may be available
This course will teach you how to use Excel lookup functions such as VLOOKUP, MATCH and INDEX. Lookup functions are used frequently to compare and combine data. 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.
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
- Creating drop down lists
- PivotTable creation
- Naming ranges
- Converting ranges to Excel tables
- Why Learn Lookup Functions?
- Getting to Grips with the VLOOKUP, HLOOKUP & LOOKUP Functions
- Exact Match VLOOKUP
- Approximate Match VLOOKUP
- HLOOKUP Function
- The LOOKUP Function
- Perform a VLOOKUP Across Different Worksheets – 3 Different Methods
- Performing VLOOKUP Across Workbooks – 3 Different Methods
- VLOOKUP and PivotTable
- Error Handling with IFERROR and IFNA
- Error Handling with ISNA
- Performing a Two Way Lookup (Vertical and Horizontal Lookup) – 3 methods
- Find a Value’s Position Horizontally or Vertically with MATCH
- VLOOKUP & MATCH
- INDEX & MATCH
- The Intersection Operator
- Lookup and Return a Complete Record
- Lookup to the Left
- Get the Data to Match with Text Functions
- Extract the Useful Portion of a Text String Using LEFT, MID & RIGHT
- Find the Position of Characters Using SEARCH, FIND & LEN
- Use TRIM to Get Rid of Unwanted Spaces
- Use the CLEAN Function to Get Rid of Unwanted Non-printing Characters
- SUBSTITUTE & REPLACE Characters
- Join (Concatenate) Data to Enable Duplicate Lookup