Excel VLOOKUP Course

Lesson 1 • Writing Your First VLOOKUP

Guides students through entering a complete VLOOKUP formula step by step. Reinforces argument order and comma placement.

Lesson 2 • Understanding Exact Match Mode

Focuses on the FALSE/0 range_lookup setting and its critical importance. Students learn why exact match is the default choice for most tasks.

Lesson 3 • What VLOOKUP Does and Why

Explains the lookup-and-retrieve concept and real-world use cases. Anchors the function's value before any syntax is introduced.

Lesson 1 • Matching Data Types Across Tables

Explains how number-stored-as-text mismatches cause #N/A errors. Students convert data types to align lookup and source columns.

Lesson 2 • Structuring Lookup Tables Correctly

Covers the rule that the lookup column must be the leftmost column in the table_array. Students learn to reorganise data to meet this requirement.

Lesson 3 • Removing Duplicates in Source Data

Shows how duplicate lookup keys cause VLOOKUP to return only the first match. Students use built-in tools to find and handle duplicates.

Lesson 1 • Locking the Table Array

Demonstrates why the table_array must be absolute when copying VLOOKUP down a column. Students practice adding dollar signs to prevent range drift.

Lesson 2 • How Excel References Work

Reviews relative, absolute, and mixed references as a foundation for locking VLOOKUP ranges. Connects reference types to formula behaviour when copied.

Lesson 3 • Locking the Lookup Value Column

Covers mixed references for the lookup_value when copying formulas across multiple columns. Students retrieve different fields from one lookup column.

Lesson 1 • Using IFERROR to Catch Errors

Teaches wrapping VLOOKUP in IFERROR to display custom messages instead of error codes. Students choose meaningful fallback values for different contexts.

Lesson 2 • Using IFNA for Targeted Error Handling

Distinguishes IFNA from IFERROR, showing that IFNA catches only #N/A errors. Students apply IFNA when other errors should remain visible.

Lesson 3 • Interpreting Common Error Codes

Explains #N/A, #REF!, #VALUE!, and #NAME? in the context of VLOOKUP. Students diagnose the root cause of each error type.

Lesson 1 • Sorting Requirements and Pitfalls

Reinforces why incorrect sort order produces silent wrong answers in TRUE mode. Students use sort tools and validation to safeguard lookup tables.

Lesson 2 • How Approximate Match Works

Explains the sorted-ascending requirement and how VLOOKUP steps through values in TRUE mode. Students trace the algorithm on a sample table.

Lesson 3 • Creating Tiered Commission Tables

Uses approximate match to assign commission rates based on sales thresholds. Students build a reusable commission calculator.

Lesson 1 • Two-Way Lookups with VLOOKUP and MATCH

Combines VLOOKUP with MATCH to look up both a row and a column simultaneously. Students retrieve values from a matrix table using two criteria.

Lesson 2 • Nested VLOOKUP for Chained Lookups

Uses the result of one VLOOKUP as the lookup_value of a second VLOOKUP to traverse linked tables. Students chain two lookups across three related datasets.

Lesson 3 • Partial Match Lookups with Wildcards

Applies asterisk and question mark wildcards inside the lookup_value for partial text matching. Students retrieve records when only part of the key is known.

Lesson 4 • Dynamic Column Index with MATCH

Replaces the hardcoded col_index_num with a MATCH formula to select columns by header name. Students build formulas that adapt when columns are reordered.

Lesson 1 • Performance Considerations for Large Files

Explains how cross-file VLOOKUP formulas slow recalculation and increase file size. Students apply strategies to minimise performance impact.

Lesson 2 • Managing and Auditing External Links

Uses the Edit Links dialog and formula auditing to track and control external dependencies. Students identify stale or broken links in a workbook.

Lesson 3 • Referencing External Workbooks

Covers the full path syntax for linking to a closed or open external workbook. Students understand when links update and when they break.

Lesson 1 • Building a Dynamic Report Template

Creates a report that updates automatically when source data changes by combining VLOOKUP with structured tables. Students test the template with new data.

Lesson 2 • Auditing and Documenting Lookup Models

Covers formula auditing, cell comments, and documentation sheets to make lookup models maintainable by others. Students produce a documented workbook.

Lesson 3 • Reconciling Two Data Sets

Uses VLOOKUP to match records between two lists and flag discrepancies. Students identify missing entries and value mismatches across datasets.

Lesson 4 • Building an Employee Data Lookup Tool

Constructs a searchable HR dashboard that retrieves employee details by ID. Students integrate error handling, data validation, and named ranges.