This data analysis session focuses on the application of lookup functions in Microsoft Excel, specifically the VLOOKUP function.Class Overview and Review

• Previous Learning: The session began by briefly reviewing material from the previous class, which covered aggregate functions (COUNT, COUNTA, COUNTIF, COUNTIFS) and conditional functions (AVERAGEIF, AVERAGEIFS, SUMIF, SUMIFS), as well as the NOT function.
• Troubleshooting: Students shared screens to troubleshoot homework assignments. The instructor highlighted common errors, such as incorrect syntax, working across multiple sheets, and misinterpreting the specific range or criteria needed for functions like AVERAGEIFS and COUNTIFS.

Lookup Functions: VLOOKUP

• Core Concept: Lookup functions are used to search for a value in a table or range and return a related value from another column or row.
• VLOOKUP Mechanics: The instructor defined the VLOOKUP (Vertical Lookup) function, explaining that it searches for a value in the first column of a table and returns a value in the same row from a specified column to the right.
• Key Rules for VLOOKUP:
  • Primary Key/Lookup Value: There must always be a lookup value (or primary key) that relates to the desired result.
  • Directionality: VLOOKUP only searches to the right of the lookup value; it cannot retrieve information from columns to the left.
  • Table Array: The table array must begin at the column containing the lookup value.

Advanced Concepts

• Cell Referencing (Locking): The instructor introduced cell referencing, explaining that locking cells (using the F4 key to add dollar signs) is necessary when a table array is fixed, especially when data is “scattered” and not in a specific sorted order.
• Nesting IFERROR: To manage errors when a VLOOKUP cannot find a specific ID (returning an #N/A error), the instructor demonstrated nesting the IFERROR function with VLOOKUP. This allows the user to replace error messages with a custom value, such as “Not Applicable”.

Next Steps

• Assignments: Students were instructed to complete remaining exercises (Problem 1C, 2A, and others) as homework.
• Upcoming Curriculum: The next class will cover HLOOKUP (Horizontal Lookup) and the INDEX and MATCH functions, followed by future modules on pivot tables, dashboard creation, SQL, and Tableau.